CODEWITHSUNDEEP
sql-server-2008ssispackagesql-agent-job
USER
USER

Reputation: 45

Execute SQL Server Agent job/package based on status of other packages

I'm really new to SSIS and I would like to automate some of my workflow. However, most of my tasks require that other jobs/packages in SQL Server Job Agent have previously been run for the day/week by other team members. My question is: how can I direct my workflow based on the status of these other packages? Control Flow doesn't have a Conditional Split tool, and I can't kick off a separate job/package from Data Flow. I can easily write a SQL statement to determine whether any or all of the related packages have been run, and even have it return a boolean value if needed, but I'm at a loss for how to make this direct the workflow to either proceed to the next step or fail the entire package if any the others haven't been run (desired result).

I really appreciate any help in this. Thank you!

Upvotes: 2

Views: 2659

Answers (1)

Jo Douglass
Jo Douglass

Reputation: 2085

In SSIS:

You can set conditions in the Control Flow by double-clicking on the arrow that links one task to another. If you set up an Execute SQL Task to run a query to check whether a package has run and then return a result into a variable, you can then set up a condition to check that variable, and only continue to the next task if it is (or isn't) a certain value.

Here's an example of how you might set up a precedence constraint so that the next step would only happen if the previous one was successful, and also a particular variable did not have a value of C:

Precedence constraint example

In SQL Server Agent:

You could also - if you want to avoid running an entire package - set something up in the SQL Server Agent job instead. You could add an initial step which checks on the status of a package, then quits the job reporting success. This does mean that if the step fails for another reason (say the database it's trying to run the select statement on is down), it will still quit and report success, so be careful - you might want to set up some other specific steps first which check for such situations.

Here's an article I used a while back when I wanted to understand how to set up a SQL Server Agent job in this way: http://sqlactions.com/2012/08/05/how-to-create-custom-schedule-for-sql-server-agent-job/

Upvotes: 3

Related Questions