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

@db042190
You should be able to do it by following steps below:

1. Launch dtexecui.exe, select SSIS package, set all the parameters, etc.
2. Go to the 'Command line' entry. you will find there what needs to be executed.
3. Switch to SSMS, and launch there T-SQL, @SQLCmd variable value should be what you copied from the dtexecui, 'Command Line' entry: EXECUTE master.sys.xp_cmdshell @SQLCmd;

Answer 2

thx yitzhak, in what path is dtexecui.exe?

Answer 3

When I am faced with this type of situation - I modify the package so it performs the necessary checks. Then just execute the package every 5 minutes. If the condition isn't met then nothing is done and the package exits. If the condition is met - the package executes and performs the work that is required.

Answer 4

Hi all. I've been denied access to this forum for no apparent reason.

If that hadn't happened I would have chosen an answer by now, Tom's first post.

I notified/complained to the Better Business Bureau North West after ending up in a wild goose chase with MS. I'll be using a different forum since this one has been a major disappointment.