The SSIS and Oracle Story Continued…

In one of my previous blogs I tried to summarize the roadblocks and the ways to address them when a SSIS Package talks with Oracle Database. Apart from the Oracle Network Bug (Fixed in Oracle Client Tools version 10.0.0.3 and above), I faced another roadblock while trying to use a configuration file for the Oracle Connection Manager. I built a simple SSIS Package with an OLEDB Connection Manager pointing to Oracle and I exported all the connection manager properties to a configuration file from the designer. When I tried to execute the package in BIDS, I got:
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”

When I dig deeper into that I found that this happens when the Oracle OLE DB provider does not recognize a property stored in the configuration file, the property in question here is “Initial Catalog”. Note that this is blank for the Oracle Connection manager in the configuration file.

I re-exported my configuration file and this time I made sure that I uncheck the “Initial Catalog” property before doing this so that it does not get added to the configuration file. Once done that, my package works like a charm!

So to summarize everything, here is the scenario:

1. Developing SSIS Package in Sql 2005 / Sql 2008 Business Intelligence Development Studio.
2. Using Microsoft OLEDB Provider to Connect to Oracle.
3. Using Oracle Client tools version 10G/11G.
4. Using Package Configuration File to set Connection Properties at Runtime (All the properties selected).
When executing Package from BIDS, following error is thrown:

"Error: 0xC0202009 at Package, Connection manager "OLEDB Provider": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Error: 0xC020801C at Data Flow Task, Oracle OLEDB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDB Provider" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Oracle OLEDB Source" (1) failed validation and returned error code 0xC020801C."

5. Re-exported my configuration file with the “Initial Catalog” property unchecked, and it worked.

 

Author : Debarchan (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Malcom (MSFT) , SQL Escalation Services, Microsoft