SSIS: How to go to next iteration in for each after a Error on actual Control Flow iteration?

Luis Lima 61 Reputation points
2020-09-15T13:02:06.573+00:00

Hi,

I have a scenario where I have a Master Packet, and this Master call another 12 others .dtsx packets through 'Execute Packet Task' in sequence inside a ForEach loop. I run my Master ETL based on Customer separation...All packets for Customer1, all packets for Customer2, all packets for Customer3, etc...

My master is configured to abort in any execution error returned by the other packets.

What I need: If a Master iteration has a error in the execution of a packet for a customer, I will stop the sequence for this customer, log in a file, and after that my ForEach ETL will go in the next customer.

ForEach Ex:
Customer 1: Packet1->Packet2->Packet3->Packet4->Packet5->Packet6->Packet7->Packet8->Packet9->Packet10->Packet11->Packet12 ( Success and go next iteration )
Customer2: Packet1->Packet2->Packet3->Packet4->Packet5( Error on packet5, record log on file, stop Customer2 and go to Customer3 )
Customer3: Packet1->Packet2->Packet3->Packet4->Packet5->Packet6->Packet7->Packet8->Packet9->Packet10->Packet11->Packet12 ( Success and go next iteration )
* and go on*

How could I do that?

Best Regards,
Luis

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,504 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-16T02:18:07.943+00:00

    Hi @Luis Lima ,

    Please do as the following steps:
    1.Add 3 Sequence Containers as 3 customers in the Foreach Loop Container.
    2.Set the Precedence Constraint between customer 2 and customer 3 as failure.
    3.Set the Propagate variable as false in the Event Handlers On Error event.
    4.Configure SSIS logs.

    Please refer to the following links and pictures:
    Precedence Constraints
    Continue a Foreach loop after an error in a SQL Server Integration Services package
    Integration Services (SSIS) Logging

    25057-cf.png

    25025-eventhandlersonerror.png

    25094-propagate.png

    25058-configuressislogs.png

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Monalv-MSFT 5,896 Reputation points
    2020-09-24T09:55:23.84+00:00

    Hi @Luis Lima ,

    Hope the following links will be helpful:

    Continue a Foreach loop after an error in a SQL Server Integration Services package

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments