Freigeben über

The SSIS and Oracle Story Continued…

  • Artikel

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

Comments

  • Anonymous
    January 20, 2010
    Thank you! This post solved my problem.

  • Anonymous
    February 01, 2010
    Thanks so much... this was a very frustrating problem and I would never have thought to leave out the "Initial Catalog" property!

  • Anonymous
    March 03, 2010
    Thanks I spend all day trying to fix this problem. Kind regards,

  • Anonymous
    March 25, 2010
    Re-exporting in my case failed. I had to override it with another workaround suggested to me inhouse. thanks for the other pointers. is there a chance for a sql service pack to have caused a breakdown ? how can i be sure ?

  • Anonymous
    March 25, 2010
    Sql Service pack should not cause this breakdown, this is in fact a "by-design" thing in SSIS. Did you start getting this error message after applying any SP to your Sql (and it was working prior to that?). One option to be sure is to test the same in an earlier build of Sql and see if the error still persists(I can bet it would). P.S. If re-exporting did not solve the issue for you, may I know what is the work around you adopted currently to address this? Thanks, Debs

  • Anonymous
    December 17, 2016
    Thanks so much,, this problem was very frustrating.This 7 years old posting save my day.I would never have thought to leave out the "Initial Catalog" property!