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
INSERT INTO ConnectionStrings
'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.