SSIS: Capture Truncation Warning from Flat File Source with "Ignore Failure" Enabled

Question

I have a 2005 SQL Server Integration Services (SSIS) package that is loading delimited flat files into some tables. A very small percentage of records have a text field that is longer than the file format specification says it can be. Rather than try to play an ongoing game of "guess the real maximum length", the customer has requested I just truncate anything over the size in the spec.

I have set the Trunctation event to "Ignore Failure" in the Flat File Source Editor, and that takes care of my extra data. However, it seems to be a completely silent truncation (it does not write any warning to the log). I am concerned that if there is ever a question about what data has been truncated, I have no way to identify it.

What is a simple way to log the fact the truncation happened?

It would be enough to identify that the file had a truncated row in it, but if I could also specify the actual row that would be great. Whether it is captured as part of the built in package logging or I have to make a special call makes no difference to me.

Answer 1

You can do the truncation yourself as part of the data flow. Set the flat file column width to a value that is very big (larger than any expected values). You can use a conditional split to identify rows that violate the length.

In the data flow path for invalid rows, you can record the information to your log. Then, you can convert the values to the valid length and merge them back with the valid rows. And, finally add the rows to the destination.

Answer 2

Before you do the actual insert have a conditional split task that takes the records longer than the actual field length and puts them into a logging table. Then you can truncate the data and rejoin them to the orginal path using a Merge or Merge Join transformation.