SSIS – Error Handling With ForEach Loop Container

Introduction

Every times my friend circle provides me some interesting story to solve. It is brain storming but interesting too.  Thanks to them to provide me such wonderful information.

Here in this article I am providing another interesting story related to error handling with ForEach Loop containers. It’s easy but has some trick to play with it. Hope it will be interesting and put some value in your professional fields.

What the Scenario is

Every SSIS article has an interesting scenario and the first thing to understand the scenario. Well understanding the scenario is 90% work done and rest is simple implementation.

We have a ForEach loop Containers. It provides us a serial number like 1 to 7. Within these containers there is Execute SQL task. It a simple task with SQL statement like

SELECT 100/?

ForEach Loop containers provide the value for(?) by a variable.

Think about a situation like this. The ForEach containers provide values like this 1,0,3,4,5,6 and 7. Now when the value of 100 is divided by Zero (0) it gives us an Error. And the Execute SQL task Failed.

With Execute SQL task the ForEach Loop containers is also failed.

We want it to continue the ForEach loop containers to next number and redirect the output of failure to another Execute SQL task.

Here we use two other Execute SQL task depends on Precedence constraint of first Execute SQL task. If the first Execute SQL task fails then it move the flow in another Execute SQL task named Failure and if not then Success.

Hope you understand the scenario

How to Do that

Step – 1 [ The Control Flow ]

 Step – 2 [ Create the table which store the Output ]

CREATE TABLE [dbo].[tbl_FailResultValues]

  (

    ResultValues  DECIMAL(18,2)

  );

GO

CREATE TABLE [dbo].[tbl_PassResultValues]

  (

    ResultValues  DECIMAL(18,2)

  );

GO

Step – 3 [ Execute SQL Task named Truncate Table ]

Here we just Truncate the Destination Table

TRUNCATE TABLE [dbo].[tbl_FailResultValues];

TRUNCATE TABLE [dbo].[tbl_PassResultValues];

Step – 4 [ ForEach Loop Containers ]

Step – 5 [ Execute SQL task ]

It’s a simple task which has SQL statement.

Result Set                   : Single Row
SQL Statement           : SELECT CONVERT(DECIMAL(18,2), 100/?);
Parameter mapping   : [User::v_LoopValue]
Result Set                   :[ User::v_Output]

Step – 6 [ Precedence Constraint ]

Step – 7 [ The Execute SQL Task names Failure ]

SQL Statement

INSERT INTO [dbo].[tbl_FailResultValues]

VALUES(?);

Step -8 [ The Execute SQL Task named Success ]

Same as step-7 but changes on SQL statement.

SQL Statement

INSERT INTO [dbo].[tbl_PassResultValues]

VALUES(?);

Step – 9 [ Run on NORMAL Condition without Changing any Property ]

When we execute the Package and when the Execute SQL Task fails the ForEach Loop containers also fails.

Step – 10 [ How to Fix it ]

This can be done by two ways. For that go to the Properly of ForEach Loop Container and change the property describe bellow

ForceExecutionResult = Success
Or
MaxErrorCount = 0

Step – 11 [ Run again after changing the Property ]

Hope you like it.

Posted by: MR. JOYDEEP DAS