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

Deployment and Execution of packages Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 8:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:21 AM
Points: 99, Visits: 578
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.
Post #1438849
Posted Friday, April 5, 2013 12:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
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.
Post #1439406
Posted Friday, April 5, 2013 12:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:21 AM
Points: 99, Visits: 578
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!
Post #1439408
Posted Wednesday, April 24, 2013 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.


Post #1445956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse