| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2002-04-05 : 11:18:22
|
| I have a series of 9 DTS packages created. Each package must finish before the next one starts or the data will be incorrect at the final destination. SQL jobs are currently scheduled to run each job individually at set times. I want one job to run all 9 packages instead of 9 jobs running 9 packages. I have created one DTS package that calls on each of the individual packages with the workflow option of "Complete" selected before starting the next package. When I run the package each package starts in the right order but they don't wait until the previous package is finished before starting. What am I missing? |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-05 : 11:27:36
|
| Can you take a snapshot of your DTS package and post it somewhere for us to see. You have to make sure you are creating a 'chain' of execution in your DTS package that calls the other 9 DTS packagesDTS1 -----> DTS2 -----> DTS3 -----> DTS4 -----> DTS5 -----> DTS6 -----> DTS7 -----> DTS8 -----> DTS9The difference between 'Complete' and 'Success' is self-explanatory. For 'Complete' the next step will run after this step completes, irregardles if it failed or was successful.For 'Success' the next step will run after this step is successful. |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-05 : 11:28:10
|
| so in this parent package you have something that has a screen representation similar to:pkg1 -----> pkg2 -----> pkg3 -----> ...and each of the arrows is blue?setBasedIsTheTruepath<O> |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 11:29:31
|
| Why not have 9 steps in your 1 job? Are you on SQL 7 or 2k?Jay<O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-05 : 11:33:45
|
| sniped!setBasedIsTheTruepath<O> |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 11:34:37
|
| Yes, I have them all linked with the workflow just like you all mentioned. If I execute each step individually and wait, it all works just fine but I don't want to execute them individually.I am on SQL 2K.When I create one SQL Agent Job, the same kind of results because it's based on success or failure -- not completion of entire task. One task may take 4 minutes to run completely. The DTS package following it may only take 5 seconds to complete. Package 2 runs immediately after the "Execute" statement within package 1 executes successfully. MBeal |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-05 : 11:39:17
|
| We're not talking about executing each step in a given package ... we're talking about the parent package where you have nine "execute package" tasks linked together.setBasedIsTheTruepath<O> |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 11:42:54
|
| I understand that. The parent package that has 9 steps. Each one linked to the other with the workflow of "Complete". Within Design mode of that parent DTS package, you can execute each step individually by clicking and executing that step only. Each one works individually. When I run the SQL agent job that calls this DTS package, it starts at the first step, and goes through each step, but it won't WAIT until the previous step is finished. I guess I am asking for some way to pause the next step until it is finished. Does that make sense?MBeal |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 11:47:30
|
Are you using sp_start_job? That is the only thing I know of that will return control to the calling batch before the actual 'thing' is complete. Calling out to DTSRun in a job step and an execute package task in a dts package both only return control to the job/dts package on completion of the called package . . .quote:
...because it's based on success or failure -- not completion...
success and failure are forms of completion . . .quote:
...Package 2 runs immediately after the "Execute" statement within package 1 executes successfully. ...
What execute statement are you talking about?Jay<O>EDIT: I posted before I read your last post. Something is wrong here, if you are using "Execute Package Tasks" and have them in series "On Complete", it does not work like you say it does.Edited by - Jay99 on 04/05/2002 11:49:35 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-05 : 11:51:37
|
| when you "play" the parent package, do you see each package kick off in turn? and, did you create the job that runs this package by selecting "schedule package" or did you write it yourself?setBasedIsTheTruepath<O> |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 11:56:36
|
| One of the statements in the first step of the parent DTS package is EXEC sp_start_job 'Restore Data' This step takes approx 4 minutes to run. The second DTS package in the parent package is EXEC sp_start_job 'DTS_BookingInfo'And so on, and so on... for 9 steps. (one or two of them are process cubes but that doesn't much matter here)Within DTS package workflow you can choose "Completion" or "Success", where in a SQL agent job you only get "Success". I guess the issue is that the job has not "completed" before the next step in the DTS package starts. It has successfully started but not successfully finished. MBeal |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 12:00:45
|
| <O>Yes, when I play the package, each step kicks off in order successfully very quickly -- obviously not waiting for the last step to fully run before starting the next step.When I created the job, I created it myself by clicking on "new job". If I go directly to the DTS package and run it (not using the job), it still runs too quickly to be finishing the previous steps.MBeal |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 12:02:30
|
| Another thing to consider is that within the DTS package, all that has to occur for success is the sp_start_job call. If the job starts, then it's successful. Whether the job subsequently FAILS will not be communicated to the DTS package. It's basically a relay race, once the first runner hands off the baton, he's got nothing to do with the rest of the race, and can't do anything if the legman drops the baton. |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 12:06:54
|
| So does the precedence of "Completion" within workflow mean completion only of the start job command? Or does it mean the completion of the job itself?MBeal |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 12:18:24
|
Yes. Maybe another race metaphor might help:DTS: "Gentlemen, start your engines" ---> engine starts ---> STEP SUCCESS | ---> engine fails ---> STEP FAILURE The point is, from the DTS package perspective, as long as the engine starts, that step not only completes, but is successful (the step completes whether the engine starts or not) So once the engine starts, DTS will move on to the next step. Now, if the race car crashes, or the engine fails on the 100th lap, obviously they don't win the race. The job FAILS, even though the step that called this job succeeded.BTW, can you combine ALL of the DTS packages into one (NOT as job steps, but DTS steps)?Edited by - robvolk on 04/05/2002 12:20:16 |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 12:19:05
|
| ok, so you are have DTS package that has 9 'Execute SQL Tasks' each of which executes the master.dbo.sp_start_job stored procedure to start a nine different jobs that each have a step that calls dtsrun to run nine different DTS packages . . .That is not the way to do what you want. Remove the 'Execute SQL Tasks' and replace them with 'Execute PACKAGE Tasks'. That will work as you anticipate . . .Jay<O> |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-05 : 12:29:18
|
| Good call Jay.MBeal.When you have your 9 Execute SQL Steps in your parent package they all do what they should do ('exec sp_start_job') and automatically give focus to the next step, regardless if the job is finished. If you do what Jay said and have your 9 steps as Execute Package steps the packages will run and will give control to the next step when the package is complete, instead of after exec sp_start_job is run.Make sense? |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 12:30:44
|
quote:
Remove the 'Execute SQL Tasks' and replace them with 'Execute PACKAGE Tasks'. That will work as you anticipate . . .Jay<O>
Jay -- where are you talking about the "Execute SQL Tasks"? Please be a little more descriptive here -- I think this may just be the answer!!!Execute package??? Where. Can you point me somewhere on books online to find out how this is done like you say?MBealEdited by - MBeal on 04/05/2002 12:35:07 |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 12:38:12
|
quote:
Jay -- where are you talking about the "Execute SQL Tasks"? Please be a little more descriptive here -- I think this may just be the answer!!!
prepare to smack yourself in the head . . .You are running SQL 2K, yeah? it should be a button on the toolbar right by the execute sql task button and the Active Script Task or it can be selected from the 'Task' menu. (Warning: I do not have a 2k installation in front of me, so I may not be 100% accurate, but the bottom line is its just another kind of object available in the dts designer .) (Warning no2: This task type is only available in 2k, not SQL7)Jay<O>EDIT: Somebody with 2k help me out here . . . Just lookup 'tasks' in Books on line . . . How could you have a DTS Package that is calling sp_start_job if you don't know what an 'Execute SQL Task' is? Are you really using DTS or are you using a Job and calling it a DTS package incorrectly . . .Edited by - Jay99 on 04/05/2002 12:40:37 |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-04-05 : 12:43:30
|
| SMACK!!!!!THUD....MBeal |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-05 : 12:44:44
|
| Did you get it working MBeal? |
|
|
|
Next Page
|
DTS "Complete" versus "Success"