SSIS Dynamic String Connection
Published: 1st April 2014
This is something that’s really useful to know. It threw me for a little while when I first thought of doing it, but now it’s something I use quite frequently and to good effect. It’s really simple to set up once you know what you’re doing.

What you need to do is to create a table of connection strings and then you’ll get the package to read through the table and use each connection in turn via a “forEach” container.

First things first, let’s log into our server, in my case a test server called “Elementary” (don’t ask) and create a table for our connections:

use AdventureWorks2012
go

if object_id('connections') is not null drop table connections
go

create table connections
(
  
connectionString varchar(200)
)


Now I’m going to enter a connection string to one of my test servers (HOMER - again, don’t ask) using Windows Authentication. You would substitute where needed:

insert into connections
select 'Data Source=HOMER;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'


Now, let’s switch to our empty package.

Firstly you’ll need 2 variables which we’ll create as follows (note that it doesn’t actually matter what’s in the ActualStringConnection value - I just like to put a default connection string in):

The reasons for these variables is that we’ll use the ConnectionStrings object as a table into which we’ll load our connection strings. Then we’ll use the ActualConnectionString for an individual record which we’ll use as the connection string. If that makes sense?

Anyway, you now need to create 2 connections. I’ve connected one to ELEMENTARY as that’s where I have my table of connections, but it’s the second which you need to be careful with so let’s create that one together:

Firstly create a New OLEDB connection and simply leave it as “(local)”, noting that this will only work if you have a default local SQL Server… otherwise you can use any other SQL Server:

Once done, select the new connection and hit F4 to look at its Properties:

What you want here is the Expressions. Select this and hit the “…” button:

Now fill it in as follows:

What we’re doing here is we’re saying that we would like this “local” connection to substitute the property ConnectionString for whatever’s in the variable ActualConnectionString.

Now we get to add some tasks to our package. So let’s first add an Execute SQL Task as follows:

Now this is a busy screen. The key points are that we’re using the ELEMENTARY connection string because that’s where we created our “connections” table.

Also we’re returning the Full Result Set as we’re trying to load the results into a variable.

And you can see the simple query that’s being used.

Now we tell the task to place the results into our object variable:

So now this task pulls out the connection strings from our table and places them into an object variable. All that’s left is to cycle through these.

Place a Foreach Loop Container into your package:

Now we need to set this up to cycle through our results.

Double click the container and set it up as follows:

What we’re doing here is telling the container that it’s accepting an ADO Enumerator, that it needs to cycle through the contents of the ConnectionString object (which we know will hold all of our connection strings) and to start at the first row in the table.

Next we tell it what to do with this data:

This tells the container that to place each value in turn into the ActualConnectionString variable.

So now all we have to do is to place whatever we want to do into the Foreach container and use the “(local)” connection and SSIS will happily do this action to every server we have listed in our connections table.

For example, I’m just going to have a simple SQL task check for a table called “bob” and if it doesn’t exist, then create it on all my servers:

And that’s all there is to it. Told you it was simple.
Comments:
Rizwan
24/12/2015 05:31:00
Hello...

Thanks... please help me how to avoid failing the package when any DB server not able to connect with Dynamic String Connection ... in my case I have 40 servers when any of server not running my package giving me error and getting failed... is there any way to bypass db connection string when the server down..
NB: Comments will only appear once they have been moderated.