THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

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

Published Wednesday, March 31, 2010 11:51 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Grumble Grumble said:

Don't get me wrong.  I'm glad to have read this, but I'm ticked at all the time I've spent in the past doing all the "yadda yadda yadda" stuff to shoehorn in the filename.

April 2, 2010 1:11 PM
 

Speedbird said:

This is a great tip! I found this just before I was going to spend a lot of time looking for a workaround.

April 7, 2010 11:33 PM
 

CaesarsSax said:

Jamie, its been a while since I've been in SSIS. So now that I'm back, you KNOW this is the first place I've hit up for the tips and tricks. I too was just about to spend the next 4 months parsing flat files into a reporting engine. This indeed did help! Thanks for taking the time to post. Even the most mundane tips save hours of work sometimes for those of us out here trying to use the tool. Ciao.

April 12, 2010 10:09 AM
 

James said:

Is this for SSIS 2005 or 2008?

May 17, 2010 8:35 AM
 

jamiet said:

James,

As far as I can remember its in both. I don't have an install of 2005 to hand but I'm certain its there.

-Jamie

May 17, 2010 9:03 AM
 

Emily said:

THIS WAS AWESOME!!! THANK YOU!!!

June 9, 2010 7:25 AM
 

Charley said:

Totally cool. Now how do I achieve the same thing with an Excel file rather than a flat file?

November 18, 2010 6:33 PM
 

jamiet said:

Charley,

Excel has the ability to save as a .csv file - I would do that. I hate using Excel as a source for data anyway - http://sqlblog.com/blogs/jamie_thomson/archive/2009/12/02/why-you-shouldn-t-open-csv-files-in-excel.aspx

-Jamie

November 19, 2010 1:30 AM
 

Pixel Geek said:

This was just what I was looking for and works like a charm! Thanks!

November 8, 2011 11:19 AM
 

Diwakar said:

Thanks a lot ! I was looking for this

February 27, 2013 8:31 AM
 

Jean Joseph said:

Thanks a lot ! Do you have more to share?

March 13, 2013 11:47 AM
 

jamiet said:

March 13, 2013 11:50 AM
 

triStar82 said:

Thanks for the tip- Only one issue though: it seems to save with entire file path.

If there a way to strip out path and import the name alone?

:-)

December 14, 2013 4:27 PM
 

jamiet said:

Hi tristar,

I'm writing from my phone so can't tell you the exact expression but you should be able to achieve what you want using a combination of FINDSTRING(), SUBSTRING() & REVERSE().

Regards

Jamie

December 14, 2013 5:35 PM
 

jacques latoll said:

This is great good post. I am trying to accomplish a similar task but I only need one part of the source file name in a destination column in my table.  In my case I am extracting data from .xml files and inserting into a table but I need to retain a unique number that is part of the source file name. Then populate that into a column in my table.

Here is a sample of my file name. my_file_123456.xml

Is there a way in my ssis pkg to parse the name and only capture the numeric value and insert into my table?

May 4, 2014 11:21 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement