How to parameterize database connection string in an SSIS package?

Question

I have a SSIS package built in Business Intellegience Development Studio which have both source and destination database specified. Now I want to use some variables to parameterize the database connections and run the package in a command line.

I try to replace the database name and sql server instance with my variable @[User::SourceDb]. @[User::SourceHost], but it failed to connect to the database.

Is that possible to paramterize the database and is there anything wrong with my variable useage? Thanks in advance!

Answer 1

There is a nice post here that details one way of doing this.

You will need to use a ConnectionManager and set the ConnectionString property of that from a Configuration Package.

The ConnectionString property is a fully qualified database connection string, like

Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

There are a few gotchas and annoyances with using configuration packages so you may have to fiddle around a bit. I'd be more detailed but it has been about a year since I last worked on an SSIS project.

If you have any specific problems, please come back and comment about what you've hit. I'll try and refire the old memories.