Friday, April 27, 2012

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.

12 comments:

  1. Why do you assign the Result Name as 0? can i just name it Connenction String and still be able to use it throughout the connection managers' string'?
    Thanks very much for your post!

    ReplyDelete
    Replies
    1. the result is 0 as it is the object returned. If you had a more than one thing coming back it would be 1, then 2... its zero based. Hope that helps :)

      Delete
  2. hi, im the same guy again :). Look when i was able to set up the connection string successfully but when im within the data flow and i want to query a random table using the connection string created ive got a massive error saying "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER" do you know how to solve this?

    ReplyDelete
  3. Hi,

    I am also facing the same problem as "Unknown" is facing. I implemented the same scenario but when package runs it fails with error "Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ConnectAzure" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed."

    Did you face this problem? If yes then please guid us about how to resolve it.

    ReplyDelete
    Replies
    1. try putting in a default value in your variable for your database - it will replace it when it runs. make sure its a database on network and not the local one. (that got me for a awhile)

      Delete
  4. Can we set the destination connection string dynamically for Bulk insert? I need to change the destination connection string and destination table dynamically.

    Thanks,

    ReplyDelete
  5. I don't believe you can do that since there is no way to map the individual columns on the fly.

    ReplyDelete
  6. Excellent! Thank you Kevin...I keep forgetting to change the expression on the connection. All better now!

    ReplyDelete
  7. Hello...

    Thanks... please help me how to avoid failing the package when any DB server not able to connect with Dynamic String Connection ... in my case I have 40 servers when any of server not running my package giving me error and getting failed... is there any way to bypass db connection string when the server down..

    ReplyDelete
    Replies
    1. perhaps a script function to verify if the server is online prior to performing your logic. I would also log that it was offline at the time to let you know it didn't run for that reason, Cheers

      Delete
  8. This comment has been removed by the author.

    ReplyDelete