The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Sunday, November 10, 2024 Login
Public

Blog posts for the month of November,2008.
Remote Execution of an SSIS Package11/21/2008 10:14:06 AM

Recently I ran across an interesting problem where we had to kick off an SSIS Package on a remote server, i found the following to help with this:

I tried psexec and it's great. A thing of beauty. Simple, clean and it just works.

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Here is a batch file that that uses it to execute an SSIS package on a remote server:

@echo off
echo Enter Username
set /p UserName=
psexec \\10.20.30.40 -u %UserName% -e dtexec /sql "\Maintenance Plans\SSIS_Package_Demo"
pause
exit

The only problem with psexec is that you have to run it using a username and password with full administrative rights on the operating system of the server where SQL Server 2005 and the SSIS packages reside.

That's not a minor problem in any type of secure envirnoment.

It was a deal-killer for us.

However, rexec is a similar utility that authenticates the user name on the remote computer before executing the specified command.


http://technet.microsoft.com/en-us/library/bb490989(TechNet.10).aspx#

The
rexec client is included with Windows 2000/XP/Vista, but they do not come with a rexecd daemon to provide the service.

Winsock does have a 3rd party (non-free)
rexecd Windows daemon, however...

http://www.denicomp.com/rexecdnt.htm

...and when you use it "
programs are executed on the Windows system in the security context of the user specified in the rexec command."

Here is a command line that runs the same package:

rexec 10.20.30.40 -l username dtexec /sql "\Maintenance Plans\SSIS_Package_Demo"

There are no simple, secure ways to remotely execute SQL Server 2005 SSIS packages short of purchasing extra SQL Server licenses.

A remote execution service like rexecd
is an absolute necessity for running SSIS packages. It's well worth the $44.95 license fee.

SSIS - Oracle to SQL Numeric11/18/2008 3:03:50 PM

SSIS, Oracle, SQL and 64 BIT - NOT Perfect together !

Recently ran into an issue where we use SSIS to pull data from Oracle and issues with converting a Oracle Numeric data type to SQL Server.  This did not go well, final corrections seemed ok, until we deployed them.

There are several solutions to this problem, it seems that having the right drivers of course helps, and so does casting / converting the data while brining it in from Oracle. 

I found a great blog entry on this, i've copied it below (as great links often disappear), and also the link:

http://blogs.microsoft.co.il/blogs/bei/archive/2008/10/18/ssis-64bit-using-oracle-provider.aspx 


SSIS 64bit – Using Oracle Provider

Hello everyone, running an Oracle Provider over a 64 bit system could be a pain in the…
The purpose of this document is to explain the main issues running an SSIS solution using Oracle as a data source under 64bit. This document would explain the problems. Offer solution and work  around procedures.


Symptoms:
When dealing with Oracle as a data-source we face two known issues:
1.            The case of the Numeric Type Casting:
                Oracle uses a data –type named numeric for numerical data.
the numeric data 
                
type  usually requires   setting precision format e.g. numeric(18,2). 
                While the OLEDB   Provider can handle the numeric type. It does so only when precision is set.when 
               
facing a meta-data of type numeric where precision wasn't set. an exception is thrown requires us
                to use casting within the Oracle query. This is not to terrible if you have single table/query. But try 
                to imagine maintaining hundreds of queries ?

2.            Provider  Data rate:
                The purpose of 64bit hardware is to accelerate the performance.
                Using a 32 bit provider  on a 64bit hardware limit the performance of the package. (OLEDB              
               
Provider uses COM objects and x86 type system).

 

Solution Scenario:

The Following solution would handle both issues. The proposed solution is to download and setup a 64 bit generic provider. The only question is which provider and what scenario ?

 Prepare Step:  Remove Previous Oracle Client and or Installations

1.       Using Oracle Install to remove all Oracle objects (including ROOT)

2.       Remove specific Oracle Assemblies from GAC:
Look under %WindowsDir% for the Assembly folder and remove all Oracle specific Assemblies (Mainly Oracle Policy and Oracle Data Provider).
beware not to remove Microsoft generic assemblies (System.* or Policy..Microsoft)

3.       Remove Oracle Registry Entries4.       Delete Oracle Folder (remaining items).


 Phase 1: Upgrading to 10g (64bit) 10.2.0.3 - Provider
The following phase would increase your provider throughput and allow you to execute a 64bit calls to the Oracle Hardware, However it wouldn't solve your Numeric casting issue.
This step is supported by two 64bit Hardware schema:

1.           
For x64 base Hardware:
                Download and Install the specific provider for x64.
               
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

 

2.            For IA64 based Hardware (Both IA64 and Itanium 2):
                Download and install the specific Itanium provider.
               
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

                Remark:               Keep in mind that specific Itanium Processors, such as Montecito                                             requires additional patch offered on the same link!.         
               


Phase 2:  Establish an Oracle Connection
1.            Use your Oracle DBA to establish your TNS_ADMIN Settings and set your             "tnsnames.ora" file.
2.            Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

 

Phase 3:  Upgrading  to Oracle Data Access Components (ODAC) 11g  - 11.1.0.6.21
This phase would solve the need to cast the SQL query to support the numeric data-type. By using the Oracle Managed Provider for .NET. a Package can be developed without the need to cast the numeric field. Performance Test shows and estimated throughput of 1Milion rows per minute.

Important Remarks:
1.            Do not remove previous version of 10.2.0.3 provider!
2.           
This phase only applies to x64 Type System. No Itanium Support at this time!!
                Itanium users, would have to solve the numeric casting problem using casting at            the sql origin.

Users of the x64 hardware, please download and install the following component:
Oracle 11g ODAC and Oracle Developer Tools for Visual Studio

http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html

Phase 4:  Establish an Oracle Connection
Since the 11g ODAC usually setup an additional Oracle Root , we need to establish additional Oracle Setting for the additional Oracle 11g Root.

1.            Use your Oracle DBA to establish your TNS_ADMIN Settings and set your             "tnsnames.ora" file.
2.            Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

3.            The Registry Setting for the 11g provider , would appear under the WOW64 entry  within the registry.

 Which Provider To Choose:

Using the SSIS designer within visual studio, be sure to choose the following provider:

Oracle Data Provider for .NETPlease do not be mistaken with the Oracle Client Provider for .NET which is a Microsoft generic provider!!!!!Q. I can't see the Oracle Data Provider For .NET within the List ?A.  Please do the following:
  1. Download the Oracle 11g ODAC and Oracle Developer Tools for Visual Studio for x86 (32 bit) and 
    extract the assemblies files.
  2. Using the GAC utility to register assemblies
  3. Go to machine.config of the 64bit which could be located at:
    %
    %WindowsDir%
    \Microsoft.NET\Framework\x64\v2.0.50727\Config
    And look for the Oracle Data Provider which is located in the "<system.data>" section.
    Copy the settings (alter to match the public key of the x86 assemblies) to the machine.config file of 
    the x86 settings.
  4. restart the server 

Do not forget to execute SSIS package using either SQL Server Job (Execute SSIS package step), or by using the dtexec command line.

 Have fun,

Eran

Rob Orchiston said:

Hi Eran, I just stumbled across your post here while looking for something unrelated on google. I spent several hours trying to get thru to a microsoft technical representative in Nth America to get some help with 64 bit providers on Oracle. The MS call routers gave me the run around and I never actually talked with an engineer, although my MSDN subscription allows for several free support calls.

As for the 64 bit Oracle Providers on our 64 bit Windows Server we couldn't get the MS or the Oracle provider for Oracle working and on Friday ended up conceeding so today the Server is being rebuilt as a 32 bit Windows Server. Our test environment is running 32 bit and SSIS packages work just fine there. I had spent several days trying to get the 64 bit providers to work but we have a production deadline and a hosted server which made resolving the issue more problematic.

# October 20, 2008 4:39 PM

Eran Sagi said:

Hi Rob,

I would like to help; could you give me more details?

The Scenario in the Blog is the result of multiple solutions all are working!

From your comments it looks like you main problem was detecting/using the providers! Here are some thoughts on that issue:

1. Did you manage to use tnsping to test if the

  oracle server is connected ? (You can also check

  Using PL/SQL or Toad)

2. Most of the 64bit providers, would work only in

  Genuine 64bit environment, meaning, you have to

  Use either the dtexec command line utility or the

  SQL Server Job (Under 64bit Instance).

3. Using Visual Studio and/or DTExecUI would only

  Activate the 32bit providers. Those providers

  Doesn't include the Oracle Data Provider for .NET,

  Unless specifically installed both 32bit and x64

  Version!

4. Did you manage to perform "Test Connection" using

  the Data source Windows of the SSIS ?

Keep in mind the running a 32bit SSIS solution would limit you to a 3GB memory space (when using the /3GB settings of the system). and limited support for multi threaded. the performance gaps are usually high!

It is recommended to try harder to achieve 64bit environment.

Since you have contacted your Microsoft Local, try to relay the Call to MCS Israel, maybe we can help here!

Regards,

Eran

# October 21, 2008 4:15 PM

Mark Frawley said:

Hello Eran

I am having this exact problem.  Before I embark on the extensive things you recommended, I have a few questions I hope you can answer:

1 - just before "Phase 4..." you say to download and install "Oracle 11g ODAC and Oracle Developer Tools for Visual Studio" but don't say anything about 32 vs 64 bit.  Later, in answering what to do if the "Oracle Data Provider For .NET" does not appear in the list, you specifically say to install the 32 bit version.  Is the overall conclusion that both are to be installed ?

2 - in your response to Rob, #2, where you say most 64 bit providers will only work in a genuine 64 bit environment and mention using dtexec or an Agent job, are you suggesting that you cannot execute packages with a 64 bit Oracle provider from within BIDS ?

Thanks for a very helpful post...

Mark

# November 17, 2008 2:36 PM

Eran Sagi said:

Hi Mark,

Well you understood corectly:

  a. You should install Both the 32bit and the 64bit

     versions. (require geniune x64 environment).

  b. 64bit SSIS Environemt is enabled during the

     execution of dtexec and SQL Server Agent job

     (64 bit version of SQL). Running the package

     from within the BIDS would result launching a

     32 bit environment.

I hope that helps,

Eran

Putting in the ceiling11/17/2008 2:57:26 PM

Putting in the ceiling...

1 day, 2 windows, 1 door10/31/2008 1:28:16 PM

Happy halloween, 1 day, 2 windows installed and 1 door, not bad for one days work.

 






Completing the outside walls11/2/2008 1:31:48 PM

The outside walls are basically completed, clean'er up, trim it up, insulate it up, caulk, caulk and more caulk...

 




Blog Home