SSIS Checking for Values in SQL Task

Question

SSIS Checking for Values in SQL Task

timothy_kramer

Ten Centuries

Points: 1358
More actions
October 29, 2009 at 2:55 pm

#139714

I have an SSIS package where I am calling a CL job on the AS400. The CL Program runs about 8 jobs on the 400 and then updates a control table with a value in the status field that it is completed. The SSIS gets called as part of a scheduled job and then moves on to various other steps. The problem I am running into is that the CL job takes about 3 hours total to run, however, it may not be 3 hours from the time of calling it from SSIS. So if I call the job as part of the job stream at 5:00 p.m., there may be other jobs scheduled to run at that same time period, which means the CL gets in line and waits. Instead of completing at 8:00 p.m., the job may not be done until 11:00 or later.
In my SSIS package, I run the CALL CL PROGRAM task and also run a Sleep command at the same time and have it set to wait 3h30m. When the sleep command completes, it moves on to an email task to alert me, and then goes on to a SQL Task that basically does a select statment to find the status update flag. The last step in this is an email task that emails another group alerting them that the CL program has completed. The problem is that the Select statement will complete regardless of what the value is in that flag and then move on to the next task. What I am trying to figure out is how can I execute the Select Table task that will only complete successfully if it finds a value of '1' in the status field?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Have you tried to use an expression in the precedence constraint coming from the sql task?

The idea would be to fork your flow based on the expression result. If the sql task does not select a 1, then send it down the "false" path to a dummy task that will fail no matter what (select * from tempdb.dbo.bogustable - just set the properties to delayvalidation=true for this task). If the original sql task evaluates to true, then it will go down the remainder of your package.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

timothy_kramer Ten Centuries Points: 1358 More actions · Answer 2

So my SQL Task has:

SELECT * FROM F55DMCTL WHERE DY55STAT = '1'

Where would I put the expression?

timothy_kramer Ten Centuries Points: 1358 More actions · Answer 3

I have searched the net for some good examples and tutorials and can't seem to get this concept to sink in. Perhaps I am just not getting the right sites, but if there is any direction that folks can lead me in, it would be much appreciated.

I have the Precedence Constraint set to use an expression, but I guess I don't have my expression set up correctly because it is not flowing through the way I am expecting. Thanks!