562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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.
We're going to create a package which can import either of two workbooks:
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.
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.
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.
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).
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.
25 Aytoun Street