June 20, 2022 at 3:58 pm
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
June 20, 2022 at 4:02 pm
please note that - this is not to ignore the row , its actually to ignore the file itslef
June 20, 2022 at 4:09 pm
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 INSERT
ed 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
June 20, 2022 at 4:18 pm
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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
June 20, 2022 at 5:24 pm
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.
June 20, 2022 at 6:53 pm
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
Change is inevitable... Change for the better is not.
June 22, 2022 at 8:24 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply