Package Configuration for Oracle Connections

Hi there,

I created some configurations for my package as Jamie described in http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx
for my Connections. I use ADO Connections as well as OLEDB Connections for Oracle (MSDAORA).

Everything works very fine, until I store passwords in the file. Yes I know, I shouldn't, but
the main idea was to keep the machine-dependent data in an external configuration file,
so you don't need to re-build your package if you want to put it onto another
machine. This scenario is only possible, if the passwords are the same for the two machines, but in my case the passwords are different (even the user which isn't the prob).

The point is: Storing passwords in a configuration file works for OLEDB Connections to
an SQL provider but NOT to the Oracle provider mentioned above.

Any suggestion would be appreciated
Fridtjof

Hi,
If you have 2 different machines then that means 2 different connection strings which means 2 different configurations. You can't use the same configuration for both.

[As an aside, you can store multiple configurations in a single XML configuration file.]

-Jamie|||Hi Jamie,

you're right that you have to use two different configuration files.

In fact I do have two different configuration files, but it seems that the pwd for the
Oracle connection is not read when the package is opened for each of that configs.

In contrast to that, the pwd I have stored for the SQL-Connection is beeing read properly.

So... :(
|||Friedel,
I am able to change the password in the config file for an ADO oracle connection. I have not used theOLEDB Connections for Oracle (MSDAORA) however. Although I do not do this in my configs normally, so I tested it through VS not in a server environment. Does it work for you when testing through VS?

Adrian
|||Hi Adrian,

thanks that helped. I tried it with an ADO Connection. That worked!

By the way: Weren't there some posts about a performance issue concerning ADO.ORA vs. OLEDB.ORA Connections?

Greetings
Fridtjof
|||Friedel,
Yeah I had posted a question about performance a few months back...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87417&SiteID=1

Since then I've had the best performance with ADO for Oracle, however I don't have any concrete tests to site for this, just an observation. Plus the added benefit of using expressions on the datareader source in a dataflow(which I believe can only use ADO), gave me some dynamic flexibility as well.

Who knows, maybe some of the MSOFT guys have some concrete info on Oracle performance now with different providers?

Adrian
|||

Friedel wrote:

Hi Jamie,

you're right that you have to use two different configuration files.

In fact I do have two different configuration files, but it seems that the pwd for the
Oracle connection is not read when the package is opened for each of that configs.

In contrast to that, the pwd I have stored for the SQL-Connection is beeing read properly.

So... :(

I didn't say that that you have to use 2 different configuration files - I said you have to use 2 different configurations. They can both go in the same .dtsconfig as long as all packages that use the file contain all the objects referenced by the configurations with it.

I am using OLE DB Connection Manager against oracle and it works fine with the password stored in the config file. here's the connection string that I'm storing:

Data Source=ssafeukp.WORLD;User ID=*****;Password=*****;Provider=MSDAORA.1;Persist Security Info=True;

-Jamie
-Jamie