Variable values stored outside of SSIS

Question

This is merely a SSIS question for advanced programmers. I have a sql table that holds clientid, clientname, Filename, Ftplocationfolderpath, filelocationfolderpath

This table holds a unique record for each of my clients. As my client list grows I add a new row in my sql table for that client.

My question is this: Can I use the values in my sql table and somehow reference each of them in my SSIS package variables based on client id?

The reason for the sql table is that sometimes we get request to change the delivery or file name of a file we send externally. We would like to be able to change those things dynamically on the fly within the sql table instead of having to export the package each time and manually change then re-import the package. Each client has it's own SSIS package

let me know if this is feasible..I'd appreciate any insight

Answer 1

Yes, it is possible. There are two ways to approach this and it depends on how the job runs. First is if you are running for a single client for a single job run or if you are running for multiple clients for a single job run.

Either way, you will use the Execute SQL Task to retrieve data from the database and assign it to your variables.

1. You are running for a single client. This is fairly straightforward. In the Result Set, select the option for Single Row and map the single row's result to the package variables and go about your processing.
2. You are running for multiple clients. In the Result Set, select Full Result Set and assign the result to a single package variable that is of type Object - give it a meaningful name like ObjectRs. You will then add a ForEachLoop Enumerator:
   • Type: Foreach ADO Enumerator
   • ADO object source variable: Select the ObjectRs.
   • Enumerator Mode: Rows in all the tables (ADO.NET dataset only)

In Variable mappings, map all of the columns in their sequential order to the package variables. This effectively transforms the package into a series of single transactions that are looped.