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

J Weezy · Accepted Answer · 2019-05-17 20:09:34Z

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.

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.
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.

Piotr Palka · Accepted Answer · 2019-05-17 18:48:37Z

0

Yes.
I assume that you run your package once per client or use some loop.
At the beginning of the "per client" code read all required values from the database into SSIS varaibles and the use these variables to define what you need. You should not hardcode client specific information in the package.

answered May 17, 2019 at 18:48

Piotr Palka

3,2351 gold badge12 silver badges20 bronze badges

Collectives™ on Stack Overflow

Variable values stored outside of SSIS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related