SSIS Flat File Source - Add Line column

Question

In my Flat File source, i have all the columns mapped to output columns with the right type and all.

After retriving the data, i check if the reference contained in the file exists in the database, if they don't, i have to log the line and write which value did not find a match.

So, in order to log the line, I would like top have a column containing the complete original line, just like the error output "Flat File Source Error Output Column".

So my question is: Is there a way to add a column to my Flat File Source, that would contain the complete line, without formatting in the data?

Answer 1

I would use a Derived Column Task and specify a new column to be added by concatenating all the columns from the text file.

For example, if your text file has the following structure:

Col1, Col2, Col3, Col4, Col5

Your derived column would specify the following Expression:

[Col1] + [Col2] + [Col3] + [Col4] + [Col5]

You could optionally add a delimiter if you wanted to break apart the columns.

This would return an addition column to be used in subsequent data flow tasks.