Extract the File Name in SSIS Data Flows using the FileNameColumnName Property

When extracting data from a flat file, it is handy to have the name of the file from which the data was retrieved. Whether you capture this information for auditing purposes, or you include it directly in the output table, the odds are good that you’ll want to have that filename for use later in the process. You could do this using an SSIS variable, but a simpler way to include the file name in the data output is to use the FileNameColumnName property of the flat file connection.

Configuring the FileNameColumnName property

The FilenameColumnName property is a flat file connection setting that lets you specify the name of the output column that will store the file name from which the data was retrieved. This is not a well-known setting, because it’s hidden in the advanced properties of the flat file source.

FileNameColumnName

This property is set to a blank value by default, in which case the file name will not be included in the output. To include the file name, simply type in the column name you’d like to use for the file name, and the full file path (not just the base file name) will be added as an additional column.

Using this property can save you an extra step of using a derived column transform for capturing the file name. This is particularly useful when you use the multiple flat file (MULTIFLATFILE) connection manager, which I will cover in the next blog post in this series.

Conclusion

The obscure but easy-to-use FileNameColumnName property of the flat file source can simplify the process of capturing the full path to the source file from which the data was loaded.

About the Author

Tim Mitchell
Tim Mitchell is a data architect and consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or training? If so, contact Tim for a no-obligation 30-minute chat.

1 Comment on "Extract the File Name in SSIS Data Flows using the FileNameColumnName Property"

  1. Awesome! Thanks. This is exactly what I needed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.