Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.83/5 (4 votes)
16 May 2012CPOL4 min read 115.8K   10   8
How can we configure our foreach loop container, even when the task inside it failed

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

Foreach File Enumerator

  1. Double click on ForEach Loop container, go to the "Collection" tab.
  2. In the "Enumerator" select "Foreach File Enumerator".
  3. Now in the "Enumerator Configuration", select the path of your source folder.
  4. 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.
  5. Now, Go to the Variable mapping tab & shown the image.

Image 2

  1. In this tab, Select the variable which we created as "varFullFile". This will contain the full path as well as filename with extension.
  2. This will configure your ForEach loop container.
  3. 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.. 
  4. Now drag & drop an Data Flow Task, name it as "DFT_TransferTextFiles".
  5. In the Data Flow, add Flat File Source & name it as "SRC_Text_Files".
  6. This will create a new Flat File connection in the Connection Manager area, Rename it as "TestConn".
  7. This is our source connection string. Now go to the properties of the TestConn i.e. the Flat File connection.
  8. Go to the expressions, from the propery select "ConnectionString" property.
  9. In the Expressions Builder, drag the variable "varFullFile" & evaluate the expression. This will show the full file path & file name.

Image 3

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

Image 4


  1. This we are doing to get different files at the destination & we will name it as "DestnTest1" where Test1 is the
    Source file names.
  2. 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
  3. This will make sure that our package fails for Truncation error.
  4. Now execute the package, as you execute you will see for the 3rd file the package fails.
  5. Now to execute the foreach loop container even after the failure we need to do the below steps.
  6. Select the Data Flow Task, go to the Event Handlers.

Image 5

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

Image 6

  1. This will ensure that the parent control i.e. ForEach loop will not know about the error in the child task.
  2. Once you set this, try executing the package. Now the same package which failed previously will run without any error.
  3. Now its the developers duty to keep track of the errors which happend.
  4. 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.
  5. 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/.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
AnswerWorked for me, thanks. Pin
rperetz23-Sep-14 7:39
rperetz23-Sep-14 7:39 
QuestionHow to schedule a parent package that call specific packages from a folder in sequence in SSIS Pin
Member 1108414415-Sep-14 6:58
Member 1108414415-Sep-14 6:58 
Questionrunning .sql files using for each loop taks Pin
Member 1107333710-Sep-14 11:38
Member 1107333710-Sep-14 11:38 
GeneralContext unclear Pin
PIEBALDconsult16-May-12 6:01
mvePIEBALDconsult16-May-12 6:01 
GeneralRe: Context unclear Pin
SalCon17-May-12 2:38
SalCon17-May-12 2:38 
GeneralRe: Context unclear Pin
Smitha_Kalakoduvath17-May-12 18:51
Smitha_Kalakoduvath17-May-12 18:51 
GeneralRe: Context unclear Pin
rperetz23-Sep-14 7:40
rperetz23-Sep-14 7:40 
GeneralRe: Context unclear Pin
PIEBALDconsult23-Sep-14 8:04
mvePIEBALDconsult23-Sep-14 8:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.