FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

jamiet — Wed, 31 Mar 2010 21:51:11 GMT

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:

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:

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

Parsing flat files using SSIS : SSIS Nugget

jamiet — Wed, 24 Mar 2010 23:11:00 GMT

Often when using SQL Server Integration Services (SSIS) you will find there is more than one way of accomplishing a task and that the most obvious method of doing so might not be the optimal one. In the video below I demonstrate this by way of an experiment using SSIS’s Flat File Source component; I show different ways that you can pull data from a flat file into the SSIS dataflow and also how the nature of the data itself can influence your choice as to how this task should be accomplished.

If you are having trouble viewing the video in your blog reader then head to http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/25/parsing-flat-files-using-ssis-ssis-nugget.aspx to see it as it is hosted on my blog!

The main point I want to get across from this video is that a little bit of creative thinking when building your dataflows can sometimes be very beneficial for performance; quite often building a solution that isn’t the most obvious might actually turn out to be the best one.

You’ll notice, if you have watched the video, that my editing skills weren’t quite up to snuff and I cut off the final few words however all I was saying was that if you have any feedback on this video then I would love to hear it either via email or preferably the comments section below. I hope this turns out to be useful to some of you.

@Jamiet

P.S. Incidentally the parsing that we do using SSIS expressions in the video would be much easier if we had a TOKENISE function in SSIS’s expression language and I have asked for the introduction of such a function on Connect at [SSIS] TOKEN(string, tokeniser_string, occurence) function. Feel free to go and vote that up if you think this feature would be useful!

Search results matching tags 'SSIS', 'flat file source', and 'nugget'

FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

Parsing flat files using SSIS : SSIS Nugget