2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Sreenu131
  • 2,476
  • 1
  • 7
  • 18
  • What do you consider error data? Bad file format? Incorrect data type? Incorrect data size? – Jacob H Mar 10 '20 at 19:15
  • What error is happenning? – KeithL Mar 10 '20 at 19:16
  • 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 Mar 10 '20 at 19:18
  • Its incorrect data type, redirect the row to error destination, but how to find which flat had contains that error – Sreenu131 Mar 10 '20 at 19:24

1 Answers1

0

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124