To enable social distancing we've completely remodelled our classroom training, launched a full range of live online courses and now provide online training-cum-consultancy.
How to create dynamic connection strings in Integration Services packages
It's often useful to make connections point to different Excel workbooks or SQL Server databases, depending on the value you set for variables or parameters. This blog shows the underlying principle - it's up to you then to apply this in your workplace!

Posted by Andy Brown on 08 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating dynamic connection strings in SSIS packages

A connection in SSIS points to a fixed file or database ... right?  Well actually, it doesn't have to; this blog explains by example how to make your connection strings dynamic.

You can work through this blog yourself by downloading and unzipping these 2 workbooks, although you'll need to know a bit about SSIS packages too.

Our worked example

We're going to create a package which can import either of two workbooks:

Genuine owls Fake owls
Genuine owls Fake owls

Creating an initial package 

Start by creating a package which imports the genuine owls, with a data viewer on the pipeline coming out of the data source to test what it's doing:

The initial package

The package could look something like this.


When you run this package (I'm using SSIS 2012) you get this:

Data viewer

The data viewer displays the list of owls found.


Creating the variable to hold the file name

The next step is to create a variable to hold the file name:

Variable with file name

I've called my variable OwlFile, and set its initial value to be Genuine.

The idea is that you'll be able to change the value of this variable from Genuine to Fake, and you'll then import a different set of owls.  You could vary this to make the variable a parameter, in which case you'd be able to run the package from your production server, varying the file imported each time.

Parameterising the connection string 

Time now to make the connection string flexible.  Right-click on the connection created, and change its properties (note that double-clicking won't work): 

Changing connection properties

Right-click on the connection to the fixed Excel workbook, and choose to change its properties.


First copy the ConnectionString property (you're going to need it soon):

Copy connection string

Copy the text in the ConnectionString property.


Now go to the Expressions property:

Expressions property

Click on the build symbol to the right of the Expressions property.


From the drop arrow, choose to create a custom expression for the ConnectionString property which will override the default one, then set an expression for this:

ConnectionString property expression

Choose the ConnectionString property from the list, and click on the build button to set the expression for this.

Paste the connection string into the box which appears:

The expression builder

Paste your connection string into the box.


Writing the connection string expression 

You now need to edit the expression.  Here's what I started with:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ajb\Genuine owls.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

Here's what I ended up with:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ajb\\" + @[User::OwlFile] + " owls.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=YES\";"

There are many pitfalls to fall into!  The main two points to notice are:

  1. You must encase the entire string in double quotation marks; and
  2. You must escape the backslash and double quote characters (precede each \ and " character with the \ character, to show they're special characters). 

You should be sure to evaluate your expression to check that it's valid before leaving the dialog box:

Evaluate expression

Click on this button to check that your expression is valid (if no error message appears, it is).


Testing your package 

You should now be able to change the value of your variable:

Fake owls variable

This time we're going for the fake owls.


When you run your package, you should now see a different set of owls:

Different owl set

This time we import the fake owls.


Note that everything shown above applies equally to SQL Server and other connection strings!

We run classroom-based and online SSIS courses which cover how to set up dynamic connection strings (and much more besides!), as well as a three-day fast-track SSIS course.  We will consider running these as online training if you have a group of people from the same company.  You can see all of our SSIS courses here.

This blog has 0 threads Add post