SSIS - ignore the file that has issue and continue with the others

Question

Post reply

SSIS - ignore the file that has issue and continue with the others

danssms

SSC Enthusiast

Points: 193
More actions
June 20, 2022 at 3:58 pm

#4052256

I have 10 text files and file 4 at nth row has special character that cant be loaded to sql server table
required is to log the issue
AND
ignore processing the 4th file and process the rest

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

danssms SSC Enthusiast Points: 193 More actions · Answer 1

please note that - this is not to ignore the row , its actually to ignore the file itslef

Thom A SSC Guru Points: 99126 More actions · Answer 2

Presumably this is within a ForEach loop container, so seems that setting the Propagate property to False of said Container is what you need; then when the data flow for that file fails (I assume it does) then the next item in the ForEach Loop Container is still processed. If the error occurs at the Destination rather than the Source, you may need to also use a Sequence Container, so that the already INSERTed rows can be rolled back.

Of course, this is all a complete guess on the next to no information we have. If you want a more concrete answer, you'll need to provide much more information.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Tim Mitchell SSCoach Points: 15681 More actions · Answer 3

Here's a post I wrote a while back about this very scenario: https://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Ed B Hall of Fame Points: 3618 More actions · Answer 4

I think there are two key things you need. Firstly the container needs to have a setting of MaximumErrorCount > 1 so that the it doesn't fail when the dataflow fails. Secondly it needs to be set not to propagate the errors to the parent. The link above has the details on how to set the Propagate system variable to false by creating an event handler that does nothing.

This is another link that includes an option to use ForceExecutionResult, but argues the Propagate method is cleaner.

https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

danssms wrote:

I have 10 text files and file 4 at nth row has special character that cant be loaded to sql server table
required is to log the issue
AND
ignore processing the 4th file and process the rest

Is it really necessary to reject the content of the entire file because one row has a bug in it or would it do to just ignore that one row and load the rest of the file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

danssms SSC Enthusiast Points: 193 More actions · Answer 6

it worked , the point is to put propagate at the dataflow level inside the foreach loop.

if you put propagate at the foreach level , errors coming from other tasks inside foreach loop will also be ignored, which was not requirement

also onerror event created at that specific dataflow scope to capture all error system variables merged them to put them in a variable , and the send email script task back in the foreach loop can pick it up