SSIS readiness check flag
I need help with the SSIS package. I have a server that populates a DB table as part of daily work. The server then writes to the process status table to inform me that it is done.
The process status table looks like this:
Job | Ready | Downloaded
myJob True False
I want to create a process that checks if the Ready flag on myJob is True and continue, or if False will sleep for 30 minutes before retrying and retrying up to 5 times.
I found this article on how to make a sleeping piece: http://blogs.conchango.com/jamiethomson/archive/2006/10/23/SSIS_3A00_-Put-a-package-to-sleep.aspx
I was thinking about using a file system task to access the process status table. Then set the variable to the Ready flag. Then use the For loop container if true will exit the for loop and continue, and if false, start hibernation, start another file system task, and set the variable to Ready. The main question I have is how to set the variable to the ready flag value?
source to share
I was able to figure it out.
I used a data flow task with an OLE DB source and a Script component inside. The Script component reads and processes database data, performs some basic if else logic, and writes a system variable.
- Writing to a variable from the 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-cost from-inside-pump-data-in-a-variable.aspx "
I later use this system variable in a for loop.
source to share
If you have a For Loop container, you can put an "ExecuteSql" task to retrieve your status value (ie false) and then use a "ScriptTask" to store it or manipulate it as needed. Then you can continue processing the contents of your for loop container (this could be a data flow task or whatever you are using).
source to share