SQL Server : SSIS Passing Variable between package

Question

I have a SSIS job that contains 4 packages to perform ETL job

in the first package we use "select newid()" function to create an unique ID for that ETL process

wondering how can I pass that variable value to all ETL package so all 4 package can use the same ID

Execute package task is out of the picture cause we want the job to have 4 steps (perform by 4 packages)

Can anyone point me a direction ?

Thanks

Just make a parent package that sets the ID then pass that as a variable to the other packages using an Execute Package Task. — Zane
– Zane, Commented Apr 10, 2014 at 20:23
Work around would be to write the value to a file in the first package and read it in with the other three. Or write the value to a table. Cheesy but it would work. — SteveB
– SteveB, Commented Apr 10, 2014 at 20:32
@user3083310 why write to a file? If you're writing anywhere why not a database? You could build a whole control stucture. — Zane
– Zane, Commented Apr 10, 2014 at 20:40
Zane, that is one way we think of, however it is not practical just to create one package for generating ID. Thanks Anyway! — user3399111
– user3399111, Commented Apr 10, 2014 at 21:06
Zane, I agree that writing to a file isn't the best way. Just coming up with options which is why I said table as well. — SteveB
– SteveB, Commented Apr 10, 2014 at 21:43

Mike Honey · Accepted Answer · 2014-04-15 03:15:00Z

2

I would create a "Master Control Package" to generate the ID, store it in a variable, then use Execute Package Tasks to call the sub-packages.

This makes your SQL Agent job definition simpler (1 step calling the MCP), and you can leverage out-of-the-box features of SSIS like parallel execution, conditional execution and restart control via checkpoints.

answered Apr 15, 2014 at 3:15

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : SSIS Passing Variable between package

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related