SSIS Check Ready Flag

Question

I need help with an SSIS package. I have a server that populates a DB table as a part of a daily job. Then the server writes to a process status table to let me know that its done.

The process status table looks like this:

Job   | Ready | Downloaded
myJob    True     False

I want to create a process that will check if the Ready Flag on myJob is True and proceed or if False it will sleep for 30 mintues before trying again and repeating up to 5 times.

I found this article on how to do the sleeping part: http://blogs.conchango.com/jamiethomson/archive/2006/10/23/SSIS_3A00_-Put-a-package-to-sleep.aspx

I was thinking of using a file system task to access the process status table. Then set a variable to the value of the Ready flag. Then have a For loop Container, if true break out of the for loop and continue and if false run the sleep then run another file system task and set the variable to the value of the Ready flag. The main question I have is how do I set a variable to the value of the ready flag?

Answer 1

If you have a For Loop container, you can place an "ExecuteSql" task to extract your status value (i.e. false), then use a "ScriptTask" to store it or manipulate it as necessary. You can then continue to process the contents of your for loop container (this may be a dataflow task or what ever your using).

Answer 2

I was able to figure it out.

I used a Data Flow Task with a OLE DB Source and Script Component inside. The Script Component read and handles the database data, does some basic if else logic, and writes to a system variable.

• Writing to a variable from a Script Component tutorial: "http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_7_2900_-2D00-Capture-a-value-from-inside-the-data-pump-into-a-variable.aspx"

I later use that system variable in a for loop.