running an msdb based pkg conditionally from sql agent

Question

hi we run 2014 std. and develop in vs 2013.

our pkgs are still in msdb, not the catalog.

i need to set up a sql agent job that runs a package every 5 minutes conditionally. The condition is based on the results of a query. I dont know if/how dtexec can be run from t-sql.

these seem to be my options...

1. two jobs , 1st calls/starts 2nd based on condition (i know how to do this)
2. 1 job, 2 steps, 1st step aborts if condition isnt met, i kind of like this because it could alert me to another problem
3. conditionally exec dtexec from 1st step (t-sql) of one job but i dont know the syntax and thus far cant find it on the web
4. control the condition from within the pkg but i hate coupling things like this in a pkg where in the future i might want to also run this pkg stand alone unconditionally in certain situations

Answer 1

I prefer 1. 1 and 2 would allow you to run it even when it does not meet the criteria.

Answer 2

Hi db042190,

To create a sql agent job that runs a package from MSDB every 5 minutes, we should create a new step and a new schedule.

When editing the step and the schedule, we can refer to the following pictures:

Best Regards,
Mona

----------

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

thx Mona, do you know how to exec a pkg from within t-sql? if pkg is in msdn, not the catalog?

Answer 4

Hi db042190,

1. May I know if you try the solution I provided above?
   In SQL Agent, we can choose the Type as SQL Server Integration Services Package and choose the Package source as SSIS Package Store or SQL Server.
   Then we can execute the ssis package from MSDB by starting the job.
   
2. We can also execute the package in MSDB in CMD.exe using DTEXEC command like: DTEXEC /DTS "\"\MSDB\New Package\"" /SERVER "\".\""
   
   Please refer to An overview of the DTExec utility in SSIS.
3. It seems that we can just execute the package in SSIS catalog using T-SQL.
   Please refer to Run an SSIS package from SSMS with Transact-SQL.

Best Regards,
Mona

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 5

thx all,

we dont use the catalog.
we already know how to select ssis and pkg name from agent ui. we do it all the time.

i havent read tom's last link yet but my question (for a very good reason) is does anybody know how to execute non catalog , sql based pkgs from t-sql? i cant find the syntax on the web.

the reason i ask is because it would offer yet one more alternative to a "conditional" exec of the pkg.