Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

SSIS Dynamic String Connection
Published: Sep 11, 2015
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

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

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.