How to parameterize database connection string in an SSIS package?

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开发者_Go百科 database.

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

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.

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

You can right-click on any connection on the project or package and "Parameterize" all the fields from connection, in project or in package parameters. Connection string differs for OLE DB connection to .NET Providers(SqlClient Data Provider) type, so be careful. We have manage to have one centralized place for connection in Project.params