Post reply

Execute SSIS after checking value in a table

  • October 6, 2008 at 7:13 pm

    #125936

    I have a table which keeps tracks of the previous run of SSIS.

    Ex:

    ProcessName StartDate EndDate

    DIM1 10/1/2008 10/1/2008

    DIM2 10/1/2008 10/1/2008

    FCT1 10/1/2008 10/1/2008

    If any of the End Date is NULL that implies the previous run had failed

    ProcessName StartDate EndDate

    DIM1 10/1/2008 10/1/2008

    DIM2 10/1/2008 10/1/2008

    FCT1 10/1/2008 NULL

    So when the DIM1 process tries to run the next time it should Error Out.

    How can this be done in SSIS? Any help is appreciated.

    Thanks

    Sreejith

  • October 6, 2008 at 7:27 pm

    #881400

    As a first step in your package you can use Execute SQL task to check the history table

    SELECT CASE WHEN EndDate IS NULL THEN convert(bit,0) ELSE convert(bit,1) END as LastRunStatus

    from SSIShistoryTable

    where ProcessName = ' '

    Store this resultset in a package level variable (say @IfLastRunSuccess) using resultset. Now you can use expression precedence constraint editor.

    Evaluation operation: Expression

    Expression @[User::@IfLastRunSuccess]

    This will make sure that the rest of the package only runs in case iflastrunsucess was true. You can also add other branch in the package to notify this failure.

    HTH

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

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