CODEWITHSUNDEEP
sql-server-2005oracle-databasessisdatabase-connection
Mutation Person
Mutation Person

Reputation: 30488

How do I specify SSIS Package Database Connection Through Package Configurations?

I have an SQL 2005 SSIS package that takes data from an Oracle DB Table, and transfers it to a SQL Server Table.

I have set up an "Oracle Provider for OLE DB" for the Oracle connection and a "SQL Native Client" for SQL Server Connection.

The Oracle and SQL connections will depend on the development and shipping stage, which are:

In the Connection Manager for Oracle, it expects the following:

So, I was wondering how I could parameterise these such that the settings are picked up depending on the server. Ideally this would be a connection string that is stored in the registry (to have commonality with the architecture of other systems in our company).

I have attempted to specify the above settings through Package Configurations. I have also tried specifying the connection string, which would look something like this:

Provider=OraOLEDB.Oracle;Data Source=DEVSERVER;User ID=devserver_user;Password=devserver_pass;PLSQLRSet=1;OLE DB Services = -2;

I have tried this through a registry setting, environment settings, and XML config file. I am mapping these item to the properties on the connection object, but the settings do not seem to hold. I.e. when I open the connection object these settings are not there.

What happens is that when I open the OLE DB source and specify the connection, it fails, because the connection object is not picking up the items in the Package Configurations.

Is there something I am missing, some setting that I have to configure. I guess I'm not sure as to what I'm not seeing anything!

Any help would be appreciated.

Upvotes: 0

Views: 4132

Answers (1)

Mutation Person
Mutation Person

Reputation: 30488

Just worked this out this myself.

This really was a case of RTFM! The first paragraph on the MSDN Package Configurations page says it all:

Typically, you create a package set properties on the package objects during package development, and then add the configuration to the package.

Still, I hope this is still of help to other RTFMers!

Upvotes: 1

Related Questions