SSIS and an Oracle OLE DB Source

Oracle…it’s in the name right? šŸ™‚
Well, let me tell you…it’s nothing like that!

In order to import and process Oracle source data in SSIS you required to:
– Download several Oracle 11g Client Tools (32 and 64 bits, will be explained a few rows down)

[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

You’re simply missing the 64-bits components it seems.
After some Google-ing, I found this post here:

Microsoft OLEDB Provider for Oracle and Microsoft ODBC driver for Oracle
If you are using Oracle data sources, you should migrate to the Oracle-supplied provider and driver. Microsoft OLEDB Provider for Oracle (msdaora.dll) and Microsoft ODBC driver for Oracle (msorcl32.dll) are built by using Oracle Call Interface (OCI) version 7. Oracle no longer supports applications that use OCI version 7 calls, and these technologies are deprecated

You need to use the provider from ORACLE (32-bits), more important…the 64-bits version:
64-bit ODAC 11.2 Release 4 (11.2.0.3.0) for Windows x64

Important: It is required to install the 32-bit Oracle Developer Tools for Visual Studio download first (is required for Entity Framework design-time features). The above download fully supports Entity Framework deployment, but does not contain design-time tools.

Perform a reboot after installing both toolkits!

After the reboot, verify that the Oracle providers are listed under Linked Servers -> Providers:

Another simple trick to do is to force the package to run in 32-bits mode:

The package you currently have open will (by default) run in 64 bit mode. The setting that controls this is a property on the project called Run64BitRuntime. To access this property, right-click on the Integration Services project in your solution explorer and select Properties. Then select the Debugging node in the editor. The default here is “true”, which means all the packages in this project will run in 64-bit mode. If you change this to “false”, all the packages will be run in 32-bit mode.

Another error message I’ve received was this one (don’t know when or how):

[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800700C1.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x800700C1 Description: “%1 is not a valid Win32 application.”.

To get this working in 64-bits mode, a suggestion would be to use the .NET providers instead:
OracleClient Data Provider

Advertisements

8 thoughts on “SSIS and an Oracle OLE DB Source

  1. Nice post. I was checking constantly this blog and I am impressed!
    Very useful info specifically the last part :
    ) I care for such info much. I was seeking this particular info for a long time.
    Thank you and good luck.

  2. This is very interesting and I think it might apply to my problem, perhaps you can help. Old environment was Oracle 10g source to SQL 2005 using SSIS data flow ( Native OLE dbMicrosoft OLE DB Provider for Oracle). Over 56 millions rows returned in an hour. (Great, right?) Now the new enviorment is Oracle 11g source to SQL 2008 r2 using SSIS data flow (Native OLE DBOracle Provider for OLE DB ). I narrowed the extract using date criteria and now it takes over 6 hours for 26 millin rows. All is excuting in 32 bit. Suggestions?

  3. Clint,
    I love your advice to set Run64BitRuntime=False
    I was having all sorts of hell trying to get SSIS to use just the 32bit Oracle Drivers, as we have 64Bit Servers and 32Bit Desktops.
    Many Thanks
    James McMorrough

  4. Thanx for ur very important info.
    can u tell me how can i schedule the same package.
    As i m having SERVER 32 bit installed oracle 32 bit and another server 64 bit having SQL server 64 bit.
    I am able create package successfully and it is running fine, but when i am scheduling this package into the job , its giving the error.
    Please help.

  5. Dear Clint,
    This is the error coming when i m scheduling from SQL Agent.
    And i have not given any password while creating Package.

    “Started: 11:37:49 AM Error: 2014-05-24 11:37:49.38 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2014-05-24 11:37:49.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2014-05-24 11:37:49.62 Code: 0xC0202009 Source: 4VIEWS Connection manager “DestinationConnectionOLEDB” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E4D Description: “Login failed for user ‘sa’.”. End Error Error: 2014-05-24 11:37:49.63 Code: 0xC00291EC Source: Drop table(s) SQL Task 1 Execute SQL Task Description: Failed to acquire connection “DestinationConnectionOLEDB”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2014-05-24 11:37:49.63 Code: 0xC0202009 Source: 4VIEWS Connection manager “DestinationConnectionOLEDB” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E4D Description: “Login failed for user ‘sa’.”. End Error Error: 2014-05-24 11:37:49.63 Code: 0xC00291EC Source: Preparation SQL Task 1 Execute SQL Task Description: Failed to acquire connection “DestinationConnectionOLEDB”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:37:49 AM Finished: 11:37:49 AM Elapsed: 0.328 seconds. The package execution failed. The step failed.”

    Regards,

  6. Pingback: How To Fix Dts:password With Error 0x8009000b Errors - Windows Vista, Windows 7 & 8

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s