SSIS – Creating a process which will check a condition, if it is not ready wait and try again.

So we had a requirement where we wanted to create a waiting for loop, so that if it was not ready it would wait, try again, and if still not ready wait and try again until it got to the Max Number of tries. We also did not want it to error whilst waiting.

Example:

• Check to see if the database is online.
• Wait for 1 minute to retry.
• Try 10 times before failing

1. The first that we need to do, is to create some Variables which will help with the process.
   1. 
2. From the above this is what they are for:
   1. Max_Count – This is how many times you want to retry
   2. State – This is our check to see if the databases are online or not.
   3. SuccessfulRun – This is used to see if it ran successfully
   4. Value_Counter – This is used as the counter.
3. Then below are the steps.
   1. The first thing is to assign a value to our Max_Count Variable.
   2. This is done using an Execute SQL Task and assigned it with the following below:
      1. 
      2. As you can see above in the SQLStatement we have set our Max_Count to 10
      3. Then in the ResultSet we have mapped our results to our Variable
         1. 
   3. Next is where we configured our For Loop Container with the details shown below:
      1. 
      2. As you can see above we have assigned it with our Value_Counter Variable.
      3. You will note that in the EvalExpression we have got our Max_Count variable as well as the SuccessfulRun
         not
         equal to zero. This will be explained later how this value is derived.
         1. NOTE: This is also required so that it will exit when it is actually
            successful.
   4. Next in our process within the For Loop is where we start with our Waiting Period as shown below using the Execute SQL Task
      1. 
      2. All that we are doing here is using a TSQL
         statement to wait for 1 minute.
      3. NOTE: The reason we do this at the start, is so that if it has to loop through, it will wait on the second
         loop before checking.
   5. Next is where we have our condition (which based on our example is to check if the database is online) as shown below:
      1. 
      2. What our SQLStatement is doing above is checking to see the State of our database.
         1. And if it is online it will have a state of equal to zero
            
      3. Then in the ResultSet we have mapped our results to our Variable
         1. 
   6. Next is where we have our Script Task which we use to populate the SuccessfulRun variable as shown below:
      1. We went into Edit the script with the following below:
      2. 
      3. As you can see above we also assigned the same variable
         SuccessfulRun to the ReadWriteVariables
      4. NOTE: We did use the Microsoft Visual C# 2012 as the script
         language.
      5. 
      6. From the above all that we added was to give the Variable SuccessfulRun = 1
         
   7. And then we set our database
      back into Multi User Mode after it was successfully back online as shown below:
      1. 
   8. The final thing that we had to do, which actually ensure that it loops through without
      erroring is to put in a precedence
      constraint
      between the Check if Database is Online and Check to see if Successful as shown below:
      1. 
      2. We then configured it with the following for the precedence
         constraint as shown below:
         1. 
4. So once this is all done it looks like the following:
   1. 
5. You can now run this, potentially
   changing your condition (Check if Database is Online) that you want to check for. As well as you can also change how long you want to wait for before
   checking
   again.