Post reply

Change Execute Package into Waiting

  • October 18, 2004 at 10:08 am

    #65695

    I am creating a DTS package that will be used to UNZIP 20 different files, upon UnZipping the Files, I perform some tasks against them and move them into another directory. I developed a Package that looks for the DONE file that represents the end of the ZIP for that file (20 ZIP, 20 DONE files).

    What I want to do is allow 2 of the packages to run at a time, and when a package discovers the DONE file does not exists, that it will reset the Execute Package task back to hold/waiting and let another one attempt to find it's DONE file.

    example:

                     ---> UnZIP Package1 ----

                     |                                |

                     ---> UnZIP Package2 ----

                     |                                |

    StartLog ---> UnZIP Package3          ----> FinishLog

                     |                                |

                      ---> UnZIP Package4 ----

                     |                                |

                     ---> UnZIP Package5 ----

    So I have MaxConcurrentSteps set to 2, Package1 and Package2 start, Package2 determines DONE file does not exists, So I want it to end and go back to a Hold Status, allowing Package3 to start, then after 15 minutes, reset Package2 to Waiting for the next available thread.

    Does anyone have good ideas on how I could do this, the reason for the Package is because the files are prefixed by a Company Code, and I can just change the Global Variable to allow for processing by Company, and instead of having to create multiple ActiveX Steps each time I add a Company to my process.

  • October 18, 2004 at 11:23 pm

    #527116

    I think you could do something in your DTS packages that has a VB Script to check for the DONE file.  Something like:

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '**********************************************************************

    Function Main()

     Dim objFS

     Set objFS = CreateObject("Scripting.FileSystemObject")

     If objFS.FileExists("d:\SQLServerData\STATUS.TXT") Then

      Main = DTSTaskExecResult_Success

     Else

      Main = DTSTaskExecResult_Failure

     End If

     Set objFS = Nothing

    End Function

    Make this the FIRST step of your Job, and set the step to goto the next step on success and QUIT showing SUCCESS on Failure.

    SQL Server will "squawk" about do you REALLY want to do this, but tell it yes.  In our situation, we're waiting for a file to arrive from another organization where they deliver the .ZIP file and the STATUS.TXT file after the .ZIP file is uploaded.

    We have the SQL scheduled job check every 10 minutes for the STATUS.TXT (in your case, the DONE) file.  When the file does arrive, the second step kills the STATUS.TXT file then proceeds through the rest of the job.  This job is scheduled to run from 6:20 AM to 7:00 PM just in case the files are late.  It takes less than a second to run the job if the STATUS.TXT file has not arrived.

    Hope this helps.


    Butch

Viewing 2 posts - 1 through 1 (of 1 total)

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