SSIS Dynamic Connections Part 1

One of the common questions that I see is, “How do I make a connection dynamic?” Well there are two different ways I typically see the need for dynamic connections. The first is for different environments and the second is to actually programmatically change the connection to load multiple files.

So the first one is rather simple and the best way to implement different connection properties per each environment is to use Configuration Files (I’m not covering the SQL 2012 options in this post). Typically what I recommend there is:

  1. Create Variables to store ServerName, DatabaseName, Username and Password
  2. Enable Configuration files for your package
  3. Map the Variables to the Configuration Files

First to setup this example I set up package that has one connection in the connection manager called DemoSQLConnection and one package called ConnDemo.

To create Variables first make sure you can see the variables pane, you can do that by right clicking on the background of the Control Flow Tab and selecting Variables.

Alternatively you can go to the Menu bar and select View  Other Windows Variables

Once you can now see your variables window let’s now create Variables for our Connection.

  1. DemoSQLServer – Used to store the server name to connect to
  2. DemoSQLDatabase – Used to store the database name to connect to
  3. DemoSQLUserName – Used to store the User Name for the connection
  4. DemoSQLPassword – Used to store the Password for the connection

Make sure you always have your variables in the correct scope and have the proper data type, for this the scope should be at the package level and the type should be string. I have also put in some default values for the variables.

If you are using windows authentication for your connection you can skip 3 and 4. (Remember the Windows user running the package must have access to the database)

Now that I have created the Variables I can enable the Configuration Files and map the variables. To do this you can right click on the background and select Package configurations or go the Menu and select SSIS Package Configurations

Once selected a window should open and you want to select the checkbox for Enable package configurations and click Add

For this example I am going to add a XML Configuration File, name it democonfig and place it in the c: drive

Click next and then add your variables to the Configuration file. You might wonder why variables and not just put the connection string in the config, two reasons I do this. First is because sometimes you create different connection types to the same server such as an OLEDB and a SQL Destination and it’s easier to just map the variables to the connection strings and define once than it is to modify both connection strings. The second reason is if the password is in a variable you can encrypt that variable (I won’t be discussing how to do that in this post)

Click next and then Name the Configuration and click Finish.

Now one last step is to map the Variables to the connection string. I prefer to do this with a variable. Create a Variable called DemoSQLConnectionString and in the properties window change the Evaluate as Expression to true and click the icon in the Expression property.

Set the Expression to

“Data Source=”+ @[User::DemoSQLServer] +”;User ID=”+ @[User::DemoSQLUserName] +”;Provider=SQLNCLI10.1;Initial Catalog=” + @[User::DemoSQLDatabase] + “;Password=” + @[User::DemoSQLPassword]

And then press Evaluate Expression to test it.

One last step now and that is set connection string of the connection to the variable. To do this we do the following. We select the connection from the connection manager and edit the expression property of ConnectionString and set it to the @[User::DemoSQLConntionString] variable.

Now you can test this by creating a Data Flow and using this connection and if you want to test to make sure it’s all working correctly go and change the password or server in the configuration file and the connection should fail.

Now as for the second way of dynamically setting connections during run time. That will be part two of this as this has already got a little on the long side.


19 thoughts on “SSIS Dynamic Connections Part 1

  1. raj March 11, 2014 / 11:55 pm

    Great Article And Configuration . It really Save me … thanks

  2. Senu July 22, 2014 / 6:12 am

    Thanks man this is help me aloot

  3. Pete September 12, 2014 / 7:42 am

    Beautifully done! Tremendous Help! Thank you!

  4. gurunathanblog December 17, 2014 / 12:31 pm

    Hi I just try what you explain. But i change the @[User::DemoSQLConntionString] value after deploy it’s not getting the value i given.for example:-;UserID=sa;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Initial Catalog=Test_Master;Password=sun. I change initial Catalog =Test_new.

    • gurunathanblog December 17, 2014 / 12:36 pm

      after change this i execute the package it’s data enter into Test_Master.

      • James Phillips December 17, 2014 / 1:55 pm

        When you changed the Initial Catalog did you change it in the connection string, variable or the configuration file.

  5. gurunathanblog December 22, 2014 / 9:33 am

    I change the Initial Catalog name before Deploy the file. I change it in DTSCONFIG File.

    • James Phillips December 22, 2014 / 6:17 pm

      When you deploy the file ensure that it is picking up the dtsconfig from the right location. If you use the deployment wizard you can specify the location on the server. If you are manually copying the package and the dtsconfig then you need to open up the .dtsx file and change the config location.

      • gurunathanblog December 23, 2014 / 5:43 am

        James Phillips – There is no issue with picking up the dtsconfig from the right location. b’cos if i change in input file path wrong. it’s show me error. so it’s selecting the correct config file correct?

  6. WoundedEgo December 22, 2014 / 4:58 pm

    Is the use of configuration files and package variables significantly different/better in 2014 than 2008r2? Or basically the same? Thanks.

    • James Phillips December 22, 2014 / 5:35 pm

      In 2014 I would recommend using Project Parameters rather than configuration files.

      • WoundedEgo December 23, 2014 / 9:57 am

        That’s very valuable information. Thanks!

  7. siddhesh March 29, 2016 / 11:18 am

    how to manage connection for multiple instances in sql server

    • James Phillips May 6, 2016 / 1:46 pm

      Are you trying to create a single connection and use it to attach it to multiple instances?

  8. abhisek September 27, 2016 / 6:08 am

    nice article

  9. Varun March 6, 2017 / 6:02 pm

    How do I make the configuration file path dynamic?

    • James Phillips March 27, 2017 / 7:04 pm

      When doing this I recommend that you only do it at execution time where you can pass in the dtsconfig location as a parameter to the dtexec.

      Would look like this. dtexec {Packagename.dtsx} /configfile {path to configfile}

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s