SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I run the same package, at the same time with different configs


How can I run the same package, at the same time with different configs

Author
Message
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
Being that this is a new problem for me I think I am having trouble grasping your solution. I can't visualize the workflow. I'd prefer to run the packages parallel but I can't seem to think come up with a non obscure way of doing it that wouldn't be a maintenance nightmare in the future.

I appreciate the help. The workflow seems to change daily as they vendor can't seem to decide on their own requirements.

I don't know if the image helps or complicates what I need to wrap up into 4 possible runs based on one variable [ExtractType] being passed in.

Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3496 Visits: 3149
Did you check your the link I provided? I thought that would do it for you.
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
Nevyn (1/15/2014)
Did you check your the link I provided? I thought that would do it for you.


Isn't the code in that link another way of setting a child package variable to the get its value from the parent package? The same thing you can do with using a configuration and setting the parent package variable to load the child.

I've been testing how to run the same package with different variables, my screenshot should help explain. In each script (Not the controller) I change the cfg_ExtractType_Parent value to a different value, either "PB_DAILY", "HB_DAILY", or "HB_ACCOUNTS".



Then in the child task I bind the variable coming in to an email subject and mail it to me. I end up with three emails with the subject "HB_ACCOUNTS"

Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3496 Visits: 3149
You're right, I thought of how to do it in 2012 (parameters), then tried to figure how to do parameters in 2008, but in your problem it would be the same parameter.

So, remaining options I can think of (none of them great):

1) Multiple copies of the package with the proc name hardcoded. Least elegant, annoying to troubleshoot, but easy.

2) Have a simple dummy table with an identity column. In the master, truncate and reseed it to start with. Then in the child, insert to it with an output in an executesql task, and use the output in an expression to switch between procedure names (if statement or data flow to a control table).

3) If you create separate SQL agent jobs for running it for each procedure, and then have a "master" job that simply calls those jobs in each step, I believe that by default it will spawn them all without waiting.NOTE: I have not tried this, but have read about it. It would let you avoid the master package altogether if it works.
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
Thanks for all the tips. But now that I am running the job daily, I can see that it needs to be run serially, since some of the procedures I was given take so long to complete. I don't want to drag down the server resources with this one job.

Hopefully we get a dedicated 2012 job server soon because I am looking forward to developing SSIS packages with the new 2012 Data tools.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214406 Visits: 41979
Phillip.Putzback (1/20/2014)
...since some of the procedures I was given take so long to complete


Actually, from what I saw of the process path in your SSIS graphics and unless they're playing against,say, DB2 linked servers, I was going to suggest that the underlying stored procedures need some work for performance.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
Jeff Moden (1/20/2014)
Phillip.Putzback (1/20/2014)
...since some of the procedures I was given take so long to complete


Actually, from what I saw of the process path in your SSIS graphics and unless they're playing against,say, DB2 linked servers, I was going to suggest that the underlying stored procedures need some work for performance.


I totally agree with you. But the queries are out of my hands.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search