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!