FileNameColumnName custom property in Flat File Source in SSIS

Using flat file source we are loading the CSV files or txt files in the database. In the previous post we saw that how we are loading the multiple CSV files using Multi flat file connection manager.

Read here: Multi flat file connection manager in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/12/multi-flat-file-connection-manager-in.html

For the audit purpose if we want to load the filename. The simplest way to get the file name to set the FileNameColumnName custom property. This will return the full path of the file. This is not a well-known setting, because it’s hidden in the advanced properties of the flat file source.

 

Click on the advance Editor.  

By default FileNameColumnName property value is blank. Here we need to give the column name.

 

Here we are Giving File_Nm . Click ok.

We are getting the file name with full path. We are using the Drive Column transformation to extract the file name from full path.

(DT_STR,100,1252)RIGHT(File_Nm,FINDSTRING(REVERSE(File_Nm),"\\",1) - 1)

  

Taking OLEDB destination and doing the mapping.

 

Package is ready to run.

See the records in the table.

  

Running this package.

  

Package executed successfully.

See the records in the Staging table. 

All files loaded successfully.  

Thanks. J