Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof|||

Friedel wrote:

Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof|||

Friedel wrote:

Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof