April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties

4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

Let me know your comments/questions.


  1. How to add a column to show file name only without file path info?


  2. Thanks a ton! Took a while searching for an answer to this!

  3. I only get the first characters. So if I have C:\flatfile.txt, I only "C" as my filename when i do it this way. How can i set it up to get only the filename withouth the path.

  4. If you only get the first character, you will have to insert a step to first perform a data conversion transformation to type STRING.

  5. seeems like u r an Indian Crap but muaaaaaaaaaaahhhhh... Man u r a true Hero... u just saves my day....

  6. So I followed the steps listed above and it worked, almost. When I execute the package the data loads and the File Name is captured in the destination table. However, when I add a second file in the root source folder it too gets loaded. Unfortunately, the same file name is passed to all new records in the destination table. How do I get the new file name to record in the destination table for the records being loaded at the time each file is being uploaded?