FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

Posted by jamiet on SQL Blog See other posts from SQL Blog or by jamiet
Published on Wed, 31 Mar 2010 21:51:11 GMT Indexed on 2010/03/31 22:03 UTC
Read the original article Hit count: 844

I saw a question on MSDN’s SSIS forum the other day that went something like this:

I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that?

I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain!

The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties:

flat file source adapter advanced properties

You’ll see in the screenshot above that I have set

FileNameColumnName=“Filename”

(it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot:

filename data viewer

So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there.

@Jamiet


© SQL Blog or respective owner

Related posts about FileNameColumnName

Related posts about flat file source