Changing DB connection from SqlServer to Oracle

Hi

I am trying to switch between oracle and sqlserver databases to read the source data. I have used a parameter file to specify the connection parameters. For SQLserver the connection looks like in the file as below:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="ab\vsh" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=dl;Initial Catalog=PM_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>dl</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>

the pacakge runs fine. But when i change the connection to Oracle, it gives me error.

The config file looks like when i put oracle connection is as follows:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="I2\vshrivas" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pm62;User ID=pcm_62;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>i2pm62</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue>pcm_62</ConfiguredValue></Configuration></DTSConfiguration>

The error which i get when running from oracle source is this:

Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:\oraTOsql-DataTX\Integration Services Project1\Integration Services Project1\Integration Services Project1\testConfig.dtsConfig".
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "dl.PM_DW": 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, OLE DB Source [1]: The AcquireConnection method call to the connection manager "dl.PM_DW" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
The program '[7264] Package.dtsx: DTS' has exited with code 0 (0x0).

Please let me know if you guys have any suggestion in what i am doing wrong.

Thanks,

Vipul

First of all, I highly recommend that you only store the ConnectionString property in the configuration file rather than the constituent parts.

Please could you make that change and see if you get the same problem. If you do, post the before & after contents of the new config file up here - it'll be much easier to read than what you have posted above.

-Jamie

|||

Jamie:

Thanks for the advice. Using only connection string in the configuration file works.

Thanks,

Vipul