This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Our worked example
Creating an initial package
Creating the variable to hold the file name
Parameterising the connection string
Writing the connection string expression
Testing your package

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

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 package could look something like this.

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

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:

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):

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 the text in the ConnectionString property.

Now go to the 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:

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:

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:

You must encase the entire string in double quotation marks; and
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:

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:

This time we're going for the fake owls.

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

This time we import the fake owls.

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

We run a two-day basic SSIS course which covers 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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR