THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

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


(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.


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



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:


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.


May 17, 2010 9:03 AM

Emily said:


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:


Excel has the ability to save as a .csv file - I would do that. I hate using Excel as a source for data anyway -


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().



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

hh said:


September 22, 2014 7:36 PM

paaaraaa said:

Thanks for the info. it was very helpful

November 10, 2014 6:10 PM

paaaraaa said:

However this does not seem to be working for me when I try to load data from different csv files and trying to capture all source file names to one target column in destination. I have made a DataFlow and have added it to ForEachloop to load all the files from a particular path. But by using the above trick I am getting name of just the first file in the target- i.e.  it is not able to get other file names in accordance to data it is loading. It is putting the first filename for everyrecord in destination-no matter from which file it is coming from.

Do you know how can I make this work with multiple file loads.

November 10, 2014 6:15 PM

paaaaraaa said:

I was able to find the solution for loading multiple files with there respective filenames to target.

Anyone looking for specific requirement like mine- have a look at the link below

November 10, 2014 7:07 PM

Evan said:

I seem to only be returning the drive letter "I" in my case. I've played arond with some of the data viewer options, but can't seem to get anything more than the drive leter. Any idea what I'm doing wrong?

January 8, 2015 10:24 AM

mikemikemikemikemike said:

Hi all, where can I access this Advanced Editor? I can't seem to find it anywhere...

February 16, 2015 1:39 PM

mikemikemikemikemike said:

nvm...found it :)

February 16, 2015 2:25 PM

Steve Grant said:

Thank you.. thank you.... thank you.

April 19, 2015 12:21 PM

Stefan said:

Is there such a simple way to do this with an Excel source?

August 25, 2015 3:51 AM

Moshé said:

Only few words: THANK YOU SO MUCH!

September 8, 2015 5:33 AM

Dilip Chauhan said:

Can someone explain me how i can get excel file name inserted in my sql table while using ssis package to import data from more then one excel files?


Dilip Chauhan

January 19, 2016 1:29 AM

Bambang said:

Hi Jamie,

I have a case, and would you or somebody help me with this case.

background :

I have files with *.txt extension on a folder. the files name  was contain store name.

example : 000001.txt in that folder it means stored number 000001.

beside that, this file contain some text inside it. that to define which one on that data was belong to, we need to define it by LENGTH.

example : 000001.txt file have this kind of data inside that files.

057577501B10Sereal 500gr118205000016000001000016000620    000000000000000000000000000000000000000000016000              00000000000                              0000000000000000000600000000012000000000000000000000000000                   0  

and this data, was delimit by LENGTH.

example :

length 1-9 was TransactionNo.

length 10-12 was TransactionType

length 13-24 was Product


i have this table that contain StroreNo,TransactionNo,TypeTran,Product columns.


i want to extract this data and load it into my table in database.

so the mapping will be like this :

FileName (from file name. exp: 000001) -> StoreNo

length 1-9 -> TransactionNo

length 10-12 -> TransactionType

length 13-24 -> Product

could some one give me a clue how to do it?

any help i'll appreciate.

Best Regards,


February 15, 2016 10:36 PM

danny said:

saved me much faffage!

August 12, 2016 4:53 AM

wayne- daytona said:

great stuff! thanks. maybe see you @ sql saturday MCO. (using sql 2008 R2). I threw in an aggregate component to get less records of just file names for loading table.

September 30, 2016 2:12 PM

Danyq said:

I seem to only be returning the drive letter "D" in my case. I've played arond with some of the data viewer options, but can't seem to get anything more than the drive leter. Any idea what I'm doing wrong?

October 20, 2016 3:27 AM

Leave a Comment


This Blog


Privacy Statement