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 Monday, January 13, 2014 12:18 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
I have a package that I want to run 3-4 copies of it at the same time with a variable that basically changes the name of a stored procedure when it runs. I am sure there is a half dozen ways but I am wondering if there is a best practice. The runs take about 2 hours a piece, so instead of the process taking 6 hours, I want to run them parallel.
We run all our packages via a CMDexec "call "\\SysDev\PROD\SQLDTS\UTIL\Extracts\exe_Forms.bat"
In the bat file I pass in the name of the package and its config.

I assume if I create on SQL JOB and have 3 steps that it would run them synchronously, which doesn't fix my problem.

The only option I can think of is to create three jobs.

I thought maybe I could use a Master package and pass in the three variables but if I have a cfg_PackageType in the child and I can't reuse it in the master if I run parallel.

The only way I can think of doing it would be ugly, copying configs around with a script task, have multiple copies of the same package.

Another gotcha or gotme is, at the end of all the process I need to create a control file. The vendor says nothing has to be in it, and it could be anything. This file is what they will use to determine that we are saying the process is complete from our end and that they can begin there process. This is one reason I was looking at wrapping the process in a master package and then after the packages ran in parallel I could use a precedence constrain to point them all to one script task to create the text file.

Ideas?

Thanks,
Phil
Post #1530407
Posted Tuesday, January 14, 2014 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,059, Visits: 1,430
Yes, if you have 3 steps in a job then they will execute serially.
It is possible to overwrite variable values contained in a config file at runtime by using the /SET option so you could do this in your batch file command line. I assume you are using a batch file as a wrapper because there are other things in the batch file than just the dtexec command?
If the dtexec command starts to get a little unwieldy then another option is to use a Command File that contains (on separate lines) the various switches and options with which you want to execute the package. Now, you can have as many Command Files as you like, each with different contents so you could call each of your 3 packages and specifiy a different Command File for each package.
There's a article on how to do just that here

Would it be possible to have 3 Execute SQL tasks (one per stored procedure) that are executed in parallel? You could either hardcode the stored proc names or have 3 variables, e.g. @StoredProc1, @StoredProc2, @StoredProc3 reference by each Execute SQL task.
If so then it makes your control file creation at the end of the process a lot easier; you could either do it within a job step immediately after the package execution job step or as a File System process within the package itself. Of course there are other ways of signifying that a completion status such as a Send Email task within the package or by populating a row\column in a table.

If doing everything in one package execution is not possible then I agree that you will need 3 SQL Agent jobs which makes checking for a completion state that much harder. You could have a job step that execute some T-SQL to check a Status column (or columns) in a table on a polling basis by using WAIT FOR - if the Status is NotComplete then wait for a certain amount of time before checking again.

I'll be interested to know what approach you decide to take!

One final note of caution: if your stored procs are querying the same set of tables (at least partially) then you have the potentially for blocking and even deadlocks (assuming you are not using NOLOCK hints or running in READ UNCOMMITED transaction isolation level).

Regards
Lempster
Post #1530589
Posted Tuesday, January 14, 2014 5:47 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
I think I will go down the road of one SQL JOB with 4 steps. I have 1 step that needs to run on a certain day of the month, so I'll put a variable int he XML config to hold that day and then check it in the first step of the process. I'll also put another variable in the config "cfg_RunPostOps" and I will set that to true for the 4th package. In the previous packages I'll keep appending the row counts to a text file and then in my "PostOps" sequence container I'll move the file to the outgoing folder when I see that cfg_RunPostOps variable set to True.

I don't know if SSIS 2012 has it, but it would be nice if that when you add a package to a Master package it would expose its variables to be set. Then I could run all three at once, point them all to step 4.

Thanks for the help,
Phil
Post #1530631
Posted Tuesday, January 14, 2014 7:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
My initial impression was to use the master package too.

Can you clarify this statement? I'm not exactly clear on the issue you encountered.

I thought maybe I could use a Master package and pass in the three variables but if I have a cfg_PackageType in the child and I can't reuse it in the master if I run parallel.


Post #1530653
Posted Tuesday, January 14, 2014 7:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,059, Visits: 1,430
Can a master package variable value be overwritten at runtime using the /SET option? I've never tried, but I see no reason why not in principle. If so then that would be the way to go - simply execute the master package with three dtexec commands using /SET to set whatever variable defines the stored proc that you want to run.
Post #1530656
Posted Tuesday, January 14, 2014 10:52 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
The problem with running Dtexec three times, assuming you meant having either three steps in a single job or three individual jobs is that the process needs to create a control file to be put in the Outbound directory after all the steps\data is there. That is the trigger for the vendors system to know that all files are ready. So I thought I could put the first 3 packages (which are the same child package) in a sequence container and then the fourth package would handle moving the control file to the outbound directory. But I can't have the master pass in the variable that the child packages uses to run the stored procedure. Because if all three children have a variable cfg_ExtractType and its source is parent then I am stuck. Unless I am misunderstanding where you are running dtext from. Is it possible to have the sql job run dtexec to call the master and then the master call dtext on each package passing in cfg_ExtractType via the /SET switch?
Post #1530787
Posted Tuesday, January 14, 2014 11:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
What version of SSIS is this? If its 2012 you can pass a parameter from the master to the child packages, so you could definitely do this.

If its 2008, i don't have experience trying, but this might be what you are looking for.
Post #1530806
Posted Wednesday, January 15, 2014 2:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,059, Visits: 1,430
Phillip.Putzback (1/14/2014)
Unless I am misunderstanding where you are running dtext from. Is it possible to have the sql job run dtexec to call the master and then the master call dtext on each package passing in cfg_ExtractType via the /SET switch?


Yes, I am talking about 3 SQL jobs (not 3 job steps because you want the execution to run in parallel) that call the master package using the /SET option to specify the value for the cfg_ExtractType variable - obviously a different value per job.
Post #1530982
Posted Wednesday, January 15, 2014 7:42 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
The problem with running them parallel is that I the code I would have to create to solve the problem with creating a signal file when they all are complete. I am sure with some more experience and time I could find a better way.

I've decided to run them serially in one job per frequency
Frequency Daily - pkg1_Step1, pkg2_Step2, pkg3_Final
Frequency Monthly - pkg1_Step1, pkg2_Step2, pkg3_Step3, pkg4_Final

The Final packages have the config variable cfg_RunPostOps = true and the PostOps step will copy the signalyyymmdd.done file to the outbound folder.

I'm still open to suggestions. Is there a way to have a master call a child and pass in the configuration file for the child to use. I don't see a configuration option for the package that can be set with an expression, only the Connection property.

Post #1531110
Posted Wednesday, January 15, 2014 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,059, Visits: 1,430
Ok, but your initial requirement was to run 3-4 copies of hte package at the same time; that's what I've been trying to give you a solution for. If you've now decided that parallel execution is not a requirment than of course that changes things!

Sometimes it pays off to challenge requirements or the 'traditional' way of doing things. I must admit that once I've found a way of accomplishing a task I will tend to gravitate towards that method every subsequent time......but sometimes taking a fresh look at the problem may result in a different (and better) solution. I need to do that more often!

Regards
Lempster
Post #1531116
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse