August 11, 2011 at 4:43 am
Hopefully a simple question - if I add multiple SSIS job steps to a SQL Agent job, do they run synchronously or asynchronously?
Thanks, Iain
August 11, 2011 at 4:49 am
Depends on the packages. If they're small and they complete relatively quickly, then they will run in order.
But SQL Agent just starts the packages. It does not wait until one is finished running before moving on to the next job step. All SQL Agent cares about is whether or not the previous job step completed before moving on to the next step. Which means, "start package X" needs to be successful. That's all that's required. The Agent does not check to see if the package completes its run before moving on to the next step.
August 11, 2011 at 5:52 am
Thanks Brandie, I'd thought as much. Perhaps you could give me an opinion on my plan to handle this in my situation?
I have a process to create that has three SSIS stages. Each stage needs to wait for the previous stage to complete before executing.
The first stage is to load files from an FTP site. There are up to 10 files in total, but I could find any combination of them on the site. Each file requires its own SSIS package. So I can't just chain stage 2 off stage 1.
So, I plan to dynamically create and execute a SQL Agent job via an sp with an execute SSIS step for each found file. The file would be logged in an audit table, with a 'Finished' flag. This would be set to true as the last step of the SSIS package.
My master sp would build and execute the job, then sit in a while loop until all 'Finished' flags were set to true.
Once the files are loaded, everything else is standard for all runs, so I can carry on as normal from there.
Does this sound like a reasonable approach? I guess I'm just a bit concerned about leaving an unterminated while loop running. I don't expect big data, so it won't run for long, but...
Edit: derp
August 11, 2011 at 7:20 am
I have a separate job that executes on a 15 minute reoccurring schedule to look for files on a certain day within a certain time frame (say, 4 hours). If, by the time the job schedule is done, there are no files, it emails people to say "Where are the files." Otherwise, it kicks off another unscheduled job.
You can use the sysjobs table (and its children tables) to find out if a job has completed before the next job starts or you can chain the jobs together, having a separate job for each package. While I believe I'm correct about the job steps not caring about whether a package is finished or not, I do believe that the job as a whole won't "finish" until all steps are executed and all work within the job is completed (meaning that a job won't say it's finished until all packages are done executing).
So if you chain Job 1 (package 1) to start Job 2 (package 2) after it's finished and then Job 3 (package 3) to start when it's finished, what you're doing should work. I think.
August 11, 2011 at 7:48 am
Great stuff. Thanks very much for the advice.
August 11, 2011 at 8:41 am
You're very welcome. Let us know the results once you have them (good or bad) as that will help others who stumble across this thread in the future.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply