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.
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.
Awesome! Thanks. This is exactly what I needed.