BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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|
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:
Here's what I ended up with:
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 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.