April 4, 2013 at 8:57 am
Hey all. I am hoping to pull upon the collective genius that frequent these forums, because I am drawing a blank.
TL;DR - How can I get round asynchronous execution of SQL Agent jobs in SSIS.
Basically we want to be able to deploy our SSIS packages so that there is one project per DW object - for example I would have a SSIS Staff Dimension project made up of packages that together pull together the staff dimension. Each project would have a SQL Agent job without a schedule. Now I will end up with perhaps 20-30 of these projects, and wanted to use the concept of a "Master Execution Project" that was able to fire off these individual SQL agent jobs while keeping the dependancies in place between the different packages.
So first hurdle is that the asynchronous nature of the execution means it'll just fire off all the packages within moments of each other and therefore breaking the dependancy links between the jobs; I read someone's blog stating rather boldly "there is nothing sql agent can't execute that SSIS can't reflect or replace". With that in mind, I wonder if there is a way to fire off the projects without sql agent but having the task that fires these projects wait for it to complete, and pass on the success/failure information to the next package.
I know this is a bit of a wall of text, and if I am banging my head against a brick if anyone else has suggestions on a deployment strategy that could help, I would love to have your input.
Thanks in advance.
April 5, 2013 at 12:13 pm
For dependent packages just add a precedence constraint to the package upon which it is dependent. For those without dependencies don't use any kind of precedence constraints. Those without precedence constraints will begin as soon as resources are available after the master package is started, those with precedence constraints will wait for the upstream job to complete before beginning.
This seems like too simple an answer for your question so I am not sure if I am missing something.
April 5, 2013 at 12:17 pm
Sorry, I wasn't clear enough - I am not trying to execute packages, I want to execute whole projects. Each project will have packages within it, deployed as a project. The project will have a SQL Agent job written for it that I can execute within another project, a 'master' project if you like that controls precedence and dependancies of project executions, but sql agent job execution in SSIS doesn't wait until the job is complete until it moves to the next package, it executes them all in very quick succession, asynchronously. Thats my issue!
April 24, 2013 at 7:27 am
To clarify what I think you are saying.
SSIS Master package runs execute SQLAgent task.
The constraint for this is satisfied when the task starts, not when it ends (which is what you want).
If so then can you get the SQLAgent task (or its associated dtsx) to output a marker file or write to a log table on completion and have the next stage of the SSIS master package wait for the log file record.
Why are you using a master SSIS project for this when the desired functionality is build into Agents where the agent can run multiple dtsx files in sequence (which is what you want to do)
What happens if the Agent job doesn't end as expected
Alternatively, why are you using SQLAgent to fire sub-packageswhen you could just ExecutePackage Task from the master package. I think using this method you can also use event bubbling for package failures from the child package back to the parent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply