How to parameterize database connection string in ssis package

Question

Hi all,

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.

Answer 2

You need to define "expressions" in the connection manager which then substitute into server/database.

You can't use variables directly quite often in SSIS

See "Using Variables in Packages" whcih links to Using Property Expressions in Packages.

And after a quick Bingle: http://sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html