SQL Server Integration Services (SSIS) – Re-run a specific Control Flow Item if it has an error
We had an issue where we only wanted to re-run a particular control flow item when it failed or had an error. We did not want to do this for our Entire SSIS Package but just for a specific Control Flow Item.
Example: Inserting Data into a Staging Table and if it fails to then re-try the insert 3 times
You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330
· I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012
NOTE: This example is using SQL Server Data Tools (SSDT) for Visual Studio 2013, and SQL Server 2014
1. Create your new SSIS Package.
2. Create the following variable:
a. 
b. The reason we have the LoopQuitParent variable is for the following:
i. It will be used to exit out of our For Loop Container when it has any other value than zero
ii. Within our For Loop Container depending on what happens to our Control Flow items, we will then set the LoopQuitParent to 1 if the Control Flow item has success.
iii. If the Control Flow Item fails then we will the LoopQuitParent to 0 (zero) so that the For Loop Container will try again.
3. Then drop in a For Loop Container into your Control Flow.
a. We renamed our For Loop Container to:
Retry Staging Insert
b. We then configured it as shown below:
c. 
d. What we are saying here is when our variable called LoopQuitParent is not equal to zero then exit.
4. Next we are going to drag in what we want to test to retry within our For Loop Container.
a. As per our example we are going to first truncate our staging table.
b. And then Insert data into our Staging table.
c. Below is a script to create our Staging Table
CREATETABLE [dbo].[Staging_SSAS_PartitionDetails](
[Name] [varchar](300)NULL,
[ID] [varchar](300)NULL,
[CreatedTimeStamp] [varchar](300)NULL,
[LastSchemaUpdate] [varchar](300)NULL,
[Description] [varchar](300)NULL,
[LastProcessed] [varchar](300)NULL,
[State] [varchar](300)NULL,
[Type] [varchar](300)NULL,
[AggregationPrefix] [varchar](300)NULL,
[StorageMode] [varchar](300)NULL,
[CurrentStorageMode] [varchar](300)NULL,
[StringStoresCompatibilityLevel] [varchar](300)NULL,
[CurrentStringStoresCompatibilityLevel] [varchar](300)NULL,
[ProcessingMode] [varchar](300)NULL,
[ProcessingPriority] [varchar](300)NULL,
[StorageLocation] [varchar](300)NULL,
[RemoteDataSourceID] [varchar](300)NULL,
[Slice] [varchar](300)NULL,
[EstimatedRows] [varchar](300)NULL,
[AggregationDesignID] [varchar](300)NULL,
[EstimatedSize] [varchar](300)NULL,
[Parent_MeasureGroupID] [varchar](300)NULL,
[Parent_CubeID] [varchar](300)NULL,
[Parent_DatabaseID] [varchar](300)NULL
)ON [PRIMARY]
d. NOTE: This table is based on the previous blog post for SSAS Partition Details
SSAS (SQL SERVER ANALYSIS SERVICES) – GETTING ALL PARTITION INFORMATION FROM SSAS DATABASE
e. Next we put in an Execute SQL Task and put in our Truncate Table Statement for the above table.
i. 
f. Next we put in our Data Flow Task to insert our data into our Staging Table
i. 
g. Next we can drag our Success Precedence Constraint between our Truncate Table and our Insert into items.
i. 
h. Next in order to populate our LoopQuitParent variable for when it succeeds we need to do the following:
i. Drag in an Execute SQL Task, and we are going to configure it so that if this Execute SQL Task is run it will set the variable LoopQuitParent equal to 1, which in turn will result in the For Loop Container exiting.
ii. We put the following in the General Section of our Execute SQL Task
iii. 
iv. NOTE: We renamed it to: SUCCESS – Set Variable LoopQuitParent Equal to 1
v. Then we click on the Result Set and configured it so that our variable would be populated
vi. 
i. Next in order to populate our LoopQuitParent variable for when it fails we need to do the following:
i. Drag in an Execute SQL Task, and we are going to configure it so that if this Execute SQL Task is run it will set the variable LoopQuitParent equal to 0, which in turn will result in the For Loop Container continuing.
ii. We put the following in the General Section of our Execute SQL Task
iii. 
iv. NOTE: We renamed it to: FAILURE – Set Variable LoopQuitParent Equal to 0
v. Then we click on the Result Set and configured it so that our variable would be populated
vi. 
j. Next we need to drag our Success Precedent Constraint to our SUCCESS – Set Variable LoopQuitParent Equal to 1 Execute SQL Task as Shown below:
i. 
k. Next we need to drag our Failure Precedent Constraint to our FAILURE – Set Variable LoopQuitParent Equal to 0 Execute SQL Task as Shown below:
i. 
5. The next part is to configure our For Loop Container so that when it does fail how many times we want it to retry.
a. NOTE: Because we are using the MaximumErrorCount Property for our For Loop Container it works like the following:
i. It will always run at least once.
ii. For example, if you wanted your package to retry 3 times, you would have to set the MaximumErrorCount = 6
1. This is because the first time it run’s it is trying to execute. And NO retry has started.
2. Then it will retry another 3 times each time starting again, making the total times it will need to be configured to 6.
iii. So the calculation for the retry is the following:
1. Total Retries X 2
2. EG: Total Retries must be 3 times
a. 3 X 2 = 6
b. We went into the Properties for our For Loop Container and set the MaximumErrorCount = 6
i. 
6. The final step is if your For Loop Container does error to not propagate to the parent or SSIS Package
a. NOTE: The reason for doing this is so that your entire SSIS Package will NOT fail during our retries.
b. Make sure that before you do the steps below you HAVE clicked on the For Loop Container
i. As with our example it is called Retry Staging Insert
c. To change this setting click on the Event Handlers tab at the top of your SSIS package
i. 
d. Next click on the following below to enable the Event Handler
i. 
ii. NOTE: As you can see above our In error event handler is for our For Loop Container called ‘Retry Staging Insert’
e. Next go to your variables, click on the Grid Options and ensure to select “Show system variables”
i. 
f. Now scroll down in your Variables until you get to the name called Propagate, with the Scope of On Error
i. Change the value to False
ii. 
g. Now save your SSIS Package.
h. Now your For Loop Container will not cause your entire SSIS Package to fail.
7. Now if you run it and it works the first time, it should run through and exit as shown below
a. 
8. Now if we cause the data flow task to fail, when looking at the Execution results we see the following to retry 3 times
a. 
b. When looking at the Truncate Table Staging_SSASPartitionDetails task we see the following
c. 
d. As you can see above it retried a total of 3 times.
e. And as you will also see due to our configuration not to Propagate to our SSIS Package even though it failed it did complete with success