You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

You get "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" error message when using Oracle connection manager in SSIS

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Symptoms

Consider the following scenario for either SQL Server 2005 or SQL Server 2008

  • You design a SSIS package using Business Intelligence Development Studio (BIDS).
  • In your package you connect to an Oracle server using an OLEDB provider for Oracle and use either Oracle client 10G or 11G.
  • You use package configuration file to set all the connection properties for the Oracle connection at runtime.

In this scenario, if you execute the package from BIDS you get the following error message

"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."

Cause

This is due to the fact that the Initial Catalog property in configuration file is not recognized by the Oracle provider. Note that this is blank for the Oracle Connection manager in the configuration file.

For example, the following XML Configuration file is generated when you use BIDS to create a SSIS package that connects to an Oracle server:

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="MyUserName" GeneratedFromPackageName="MyPackage" GeneratedFromPackageID="<guid>" GeneratedDate="2/22/2010 9:00:00 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[MyConnectionManager].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MyServerName;User ID=MyAccount;Password=MyPassword;Initial Catalog=;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Resolution

Remove the check box for Initial Catalog when creating or editing the configuration file through the BIDS Designer.

For example the fixed version of the example Configuration file  shown in the Cause section will be as follows:

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="MyUserName" GeneratedFromPackageName="MyPackage" GeneratedFromPackageID="<guid>" GeneratedDate="2/22/2010 9:00:00 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[MyConnectionManager].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MyServerName;User ID=MyAccount;Password=MyPassword;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
More Information
  • 244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 2009312 - Last Review: 02/24/2010 04:41:00 - Revision: 2.0

  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • KB2009312
Feedback
p;t=">