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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Barry CunneyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.