SSIS - Using variables in connection managers

Hi Experts,

I have several clients that all use the same SSIS packages.  I'd like to use just one SSIS package for all my clients.  Instead of creating a new ssis package each time we add a client I want to use a variable inside one master package.

I created an execute task that queries a static table to get my client IDs.  The result set is "Full result set" and the result set is configured to my variable "User::Store" with a result name of 0.  This execute task links to a foreach loop container with all the tasks and data flows that are normally needed for each client.

The variables that show up for the ssis package are just the Store variable with a data type Object.  I assume since I'm returning multiple rows it needs to be an object?

The issue is with the flat file and ole db connection manager.  I'm trying to use the Store variable in the connection manager's expression.  The flat file expression for ConnectionString looks like:

"\\\\myserver\\csvs\\" + @[User::Store] + "\\file.csv"

and the ole db connection's expression is set to Initial Catalog = @[User::Store]

The error says I can't use an object as an expression.  How do I setup the connection managers to use this variable?  I can set the first execute task to a single row result set and change the Store variable to string and it executes for all the rows returned but only the first result (very first Store ID returned by the query).  Help!
Who is Participating?
Barry CunneyConnect With a Mentor Commented:
Hi Jay
So I would use the
as a good reference

Get a simple version of your scenario working first, i.e. just a package that loops through each record in clientlist, stores the Store field value in a variable and display this in a messagebox to prove it is getting a value.
Then when you are sure the Store variable is getting a value on each iteration, you can then move on to the next version, i.e using the Store variable in Connection manager connection string expression -
Come back with any specific questions
Barry CunneyCommented:
Hi Jay,
Would it be possible to post up a print screen of your SSIS package design?

I am not clear on the objective/concept of setting the connection managers dynamically in your scenario - Is it that when your SSIS package is run on different machines or by different people it should dynamically work out which store's database to connect to?

Here is a good article which explains in general how you would return a result set by executing a SQL command, then iterate through the result set, and on each iteration dynamically assign field values to SSIS variables.

The key components used are an Execute SQL Task and a For Each Loop
The important point is that in the Execute SQL task configuration you specify Full Result Set and that you specify a result set variable of type Object. *** The Result Name in the Execute SQL Task should be 0 ***
In the For Each Loop you specify ADO Enumerator and specify the result set object variable populated by the Execute SQL task above and it is on the Variable Mapping page that you map variables(e.g. StoreID) to columns in the result set.
A good tip for debugging is to place a Script Task inside the For Each Loop and get it to display the value of the StoreID variable in a message box.
jay-areAuthor Commented:
Thanks for the response.  I can grab a few screen shots of the package in a few hours.  

The reason I need to use a variable for my connection managers (flat file & ole db) is because with each client the folder that I use to import a CSV changes, and for each client the initial catalog of the ole db connection changes as well.  As it loops through the results of my initial execute task (select clientID from clientlist), it needs to use the Store variable in the connection manager.  I hope that makes sense.
jay-areAuthor Commented:
Thank you for the article.  I breezed over it the first time and missed the ADO setting for the enumerator.  Once that change was made the container loops through my client IDs like it should.  I also added a variable mapping in the loop to a new clientID variable that is a string.  I can use that variable in my connection managers without any issues.  Perfect!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.