none
Wait and Retry RRS feed

  • Question

  • Hi
    I have a SSIS Pkg, that has various tasks in it. The very first task reads a SQL Tbl called Maintbl and based on
     the recs returned it enters a loop and runs other tasks one at a time . If no recs found from that tbl, it runs a
    sql task that calls a stored proc, just logging that there are NO files.
    My issue is that if the  first two  tasks above the foreach loop container fails, ie it cannot read the SQL Maintbl, table
    for whatever reason ie Connection issues,
    or tbl does not exist or whatever reason, I need to do the foll:

    1. Retry connection 3 times with
    2. A delay of 5 minutes.
    Pl advise.

    Tuesday, August 18, 2009 2:16 PM

Answers

  • Add a ForLoop Container to your Package before your ForEachLoop.
    Add an Variable to the scope of your ForLoop called RetryCount.
    Configure Your ForLoop like:
    InitExpression: @RetryCount = 1
    EvalExpression: @RetryCount <= 3
    AssignExpression: @RetryCount = @RetryCount +1

    Then add a SequenceContainer to this ForLoopContainer and put your 2 Task inside this SequenceContainer.

    Then add 2 ScriptTask behind this Seqeuncecontainer and add one contraint to each of them. One with Success one with Failure.

    The Scripttask with Succes gets a WriteVarialbe RetryCount:

    then inside add:
    DTS.Variables("RetryCount").Value = 4

    In the other task add also add RetryCount as ReadOnlyVariable. In there add:

    IF DTS.Variables("RetryCount").Value=3 Then
    Throw new Exception("Retried 3 Times to Establish Connection, failed 3 Times")
    End IF
    System.Threading.Thread.Sleep(5000)

    Then Connect the ForLoopContainer with your For Each Container with a Success COnstrained

    Set the MaxFailureCount of the ForLoopContainer to sth like your retrycount

    • Marked as answer by Desigal59 Wednesday, August 19, 2009 4:20 AM
    Tuesday, August 18, 2009 2:54 PM

All replies

  • Hi,
    just use the script task  and add the following code there

    For

     

     


    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    intRetryCount = 1 To intRetry
     Try

      -- add your code for positive run

       intRetryCount = intRetry
       Catch ex As Exception
        If Not ex.Message.Contains("Could not find a part of the path") Then -- add you exception condition
        Throw ex
        Else
        Thread.Sleep(5000) --delay code
        End If
        End Try
    Next

    Thanks-
    • Proposed as answer by Sudeep Raj Tuesday, August 18, 2009 2:39 PM
    Tuesday, August 18, 2009 2:28 PM
  • Add a ForLoop Container to your Package before your ForEachLoop.
    Add an Variable to the scope of your ForLoop called RetryCount.
    Configure Your ForLoop like:
    InitExpression: @RetryCount = 1
    EvalExpression: @RetryCount <= 3
    AssignExpression: @RetryCount = @RetryCount +1

    Then add a SequenceContainer to this ForLoopContainer and put your 2 Task inside this SequenceContainer.

    Then add 2 ScriptTask behind this Seqeuncecontainer and add one contraint to each of them. One with Success one with Failure.

    The Scripttask with Succes gets a WriteVarialbe RetryCount:

    then inside add:
    DTS.Variables("RetryCount").Value = 4

    In the other task add also add RetryCount as ReadOnlyVariable. In there add:

    IF DTS.Variables("RetryCount").Value=3 Then
    Throw new Exception("Retried 3 Times to Establish Connection, failed 3 Times")
    End IF
    System.Threading.Thread.Sleep(5000)

    Then Connect the ForLoopContainer with your For Each Container with a Success COnstrained

    Set the MaxFailureCount of the ForLoopContainer to sth like your retrycount

    • Marked as answer by Desigal59 Wednesday, August 19, 2009 4:20 AM
    Tuesday, August 18, 2009 2:54 PM
  • Not sure where you are retrying the step?
    Tuesday, August 18, 2009 4:00 PM
  • In the for loop the entire package will be re run if the condition is not met.
    Hope this helps !! Please mark the post(s) as “Answered / Helpful” that helped you - Sudeep
    Tuesday, August 18, 2009 4:14 PM