Link to home
Start Free TrialLog in
Avatar of jay-are
jay-areFlag for United States of America

asked on

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!
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

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.
http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

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.
Avatar of jay-are

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jay-are

ASKER

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!