Dynamic Connection Strings in SSIS

If you need the ability to change connection strings in your SSIS packages without opening each one and hard coding a string, then dynamic connection strings are your answer. There are many different ways to do this, including Package Configurations and Parameters (2012), but here I’ll be explaining how to use variables to dynamically change the connection string using information stored in a flat file or database table that can be manipulated without opening the SSIS package. This make server renames / upgrades / moves much easier since the information only has to be changed in one place.

Starting out, we’ll need the source data. I’ve created a table with 2 columns, ConnectionName and ConnectionString along with some test data.

CREATE TABLE ConnectionStrings
    (ConnectionName VARCHAR(256)
    ,ConnectionString VARCHAR(256))
INSERT INTO ConnectionStrings
    (ConnectionName
    ,ConnectionString)
        VALUES  ('<ConnectionName>',
        'Data Source=<Server>;Initial Catalog=<Database>;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
    )
            

Once the data has been populated in the table, we can open up a new SSIS project in the SQL Server Data Tools (2012) or BIDS (2008/2005) application and add a new OLE DB Connection. At this point it doesn’t matter what connection string is used since it will be overwritten at execution time, but for testing purposes you will want to point it to your test system.

Next ,we will create a new variable scoped to the package called SourceConnectionString with a Data Type of String.

Now, select the Source connection in the Connection Manager and expand Expressions. This is where we’ll assign the SourceConnectionString variable to the ConnectionString of the Source connection via expressions.

Finally, we need to populate the SourceConnectionString variable by querying the connection string table we setup in the first step. Once we have the results from the query, we’ll assign the results to the variable, which in turn assigns a connection string to the Source connection. This can be accomplished by doing the following.

Under the Execute SQL Task Editor, the result set should be set to Single Row and the SQL Statement should read “Select ConnectionString From ConnectionStrings”

Next, under Result Set, assign Result Name 0 to the variable that you created for the connection string.

Now that everything is set, the Source connection string value will be filled with the value that you placed in the ConnectionStrings table.