Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How can I run the same package, at the same time with different configs Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 10:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 10:03 PM
Points: 47, Visits: 174
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.
Post #1531214
Posted Wednesday, January 15, 2014 5:01 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 605, Visits: 2,104
Did you check your the link I provided? I thought that would do it for you.
Post #1531353
Posted Sunday, January 19, 2014 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 10:03 PM
Points: 47, Visits: 174
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"


Post #1532452
Posted Monday, January 20, 2014 7:17 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 605, Visits: 2,104
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.
Post #1532625
Posted Monday, January 20, 2014 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 10:03 PM
Points: 47, Visits: 174
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.

Post #1532647
Posted Monday, January 20, 2014 2:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532792
Posted Monday, January 20, 2014 2:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 10:03 PM
Points: 47, Visits: 174
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.
Post #1532794
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse