How to find which flat file contains data errors while loading multiple flat files using Foreach File enumerator in SSIS

2
votes

I have a question in SSIS. For an instance, I have 100 flat files of the same metadata columns to be loaded using an incremental load, but my question is how can we find which flat contains error data while loading using for each loop container. Any solution can be appreciated

1
sql-serverssisetlflat-fileforeach-loop-container
What do you consider error data? Bad file format? Incorrect data type? Incorrect data size? - Jacob H
What error is happenning? - KeithL
The trick I use is don't let that error happen. Trap it and then redirect it to error path. So if it is truncation. Let in a extremely wide column and check if it meets size constraint. If it fails, then redirect it. - KeithL
Its incorrect data type, redirect the row to error destination, but how to find which flat had contains that error - Sreenu131

1 Answers

0
votes

Simplest solution: Since you are using a Foreach Loop container, then the file path is mapped to a variable. You can simply add a Derived Column Transformation and use this variable within the expression as following (assuming the variable name is FilePath):

@[User::FilePath]

Then insert it with the erroneous rows.