In SSIS how to continue a for each loop container even if its inner tasks fails (Usage of Propogate Property in SSIS)

Introduction

How can we configure our foreach loop container, in case any of the inner task fails. This scenarios might not be common but at times we may need to configure our foreach loop container to such scenarios.

Background

Suppose you have an application, where we need to loop through some log table based on the IDs & load data into the destination. Now, in this scenario there might be the stiuation where some of the tasks in foreach loop container may fail. But your requirement is even though the inner tasks failwe should process the other source which are available with us.

Using the code

1. Add a new package, name it as "ContinueExecutionOfFailedForEachLoop".
2. In this example, I am using Foreach loop for File Enumerator.
3. Create a 2 variables as "varFullFile", this will store the full file path when you are looping.
   e.g. "C:\MSBI_Training_Materails\Source\Test.txt"
4. Another variable as "varFile", here we will store the source path.
   e.g. "C:\MSBI_Training_Materails\Source\
   I will tell you more why we created 2 variables.
5. Add a ForEach loop container & name it as "FELC_Text_Files",Now we will configure the ForEach
   Loop container as below screen shots

6. Double click on ForEach Loop container, go to the "Collection" tab.
7. In the "Enumerator" select "Foreach File Enumerator".
8. Now in the "Enumerator Configuration", select the path of your source folder.
9. In "Files", specify the file type as "Test*.txt". This step will make sure that from the source folder only the files which start from "Test" & extension ".txt" will be processed.
10. Now, Go to the Variable mapping tab & shown the image.

11. In this tab, Select the variable which we created as "varFullFile". This will contain the full path as well as filename with extension.
12. This will configure your ForEach loop container.
13. Now we will add some source file in our source folder with below structure.
    
    

    ID,Val 
    1,2
    2,3
    3,4
    4,5
    5,6
    6,7

    
    You can create some 4 to 5 source file with the same structure & name each file as Test1,Test2 etc.. 
14. Now drag & drop an Data Flow Task, name it as "DFT_TransferTextFiles".
15. In the Data Flow, add Flat File Source & name it as "SRC_Text_Files".
16. This will create a new Flat File connection in the Connection Manager area, Rename it as "TestConn".
17. This is our source connection string. Now go to the properties of the TestConn i.e. the Flat File connection.
18. Go to the expressions, from the propery select "ConnectionString" property.
19. In the Expressions Builder, drag the variable "varFullFile" & evaluate the expression. This will show the full file path & file name.

20. Now drag & drop Flat File Destination & rename it as "DESTN_Text_Files".
21. This will also create new Flat File connection in the Connection Manager area, rename it as "Destn Test".
22. Now go to the Properties of "Destn Test" connection string, again select the ConnectionString property.
23. Give the below expression in the Expression Builder
24. @[User::varDestnFileName]+ "Destn"+REPLACE( @[User::varFullFile], @[User::varFile] , "")

25. This we are doing to get different files at the destination & we will name it as "DestnTest1" where Test1 is the
    Source file names.
26. The source file which we created earlier now we will introdue some error in the 3rd file as below.
    
    

    ID,Val
    1,2222222222222222222222222222222222222222222222222222222222222222222
    2,3333333333333333333333333333333333333333333333333333333333333333333

27. This will make sure that our package fails for Truncation error.
28. Now execute the package, as you execute you will see for the 3rd file the package fails.
29. Now to execute the foreach loop container even after the failure we need to do the below steps.
30. Select the Data Flow Task, go to the Event Handlers.

31. Enable the OnError Event handler.
32. In the Event Handler tab, click on the "Show System Variables".
33. Now select the "Propogate" property & change its value to "False".

34. This will ensure that the parent control i.e. ForEach loop will not know about the error in the child task.
35. Once you set this, try executing the package. Now the same package which failed previously will run without any error.
36. Now its the developers duty to keep track of the errors which happend.
37. Please note suppose you have some 4 to 5 task in ForEach loop container, then in such scenarios we can add an Sequence Container. All the child task will be placed in this Sequence container.
38. Then we will enable the Event handler on the sequence container & make its Propogate property to False. This is the short cut to achieve this task or else we have to individually configure each & every child tasks Propogate property..

Points of Interest

When we set the Propagate property to False, the parent control will not come to know about the failure in the child task

Links

http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/.