Search code examples
sql-serverssisetlforeach-loop-containerexecute-sql-task

Extract filename and update table in SSIS Foreach Loop Container

I have an SSIS package that has this Foreach Loop Container(with File Enumerator) that reads from a folder with multiple CSVs file and then upload the data into a flat table.

This is working fine but my problem is trying to also extract the filenames of the file and then populate the last column in the flat table after inserting a row.

I have also added an execute SQL task after the Data Flow task(within the ForEach Loop Container) hoping that it would execute straight away before the loop goes to the next file, but unfortunately this is not the behavior.

The execute SQL task will only execute after all the data in all the files is read. Is there a way to do this filename update row by row, such as read a row from the CSV file, insert this row into the table, update the row in the filename column in the same table, and then read the next row? Continue this way until the CSV is read completely then move to the next CSV file and do the same.

I have a programming background and slightly feel that nested for loops could be a way but not sure how to achieve this in SSIS. The setup of my ForEach loop container is shown below:

Contro flow

Data flow

Solution

  • Why using an Execute SQL Task to add the file name?!

    You can simply add the file name into the data pipeline using one of the following methods:

    (1) Using the FileNameColumnName property

    In the Data Flow Task, you can simply right-click on the Flat File Source, and click on the Show Advanced Editor option.

    enter image description here

    In the Flat File Source Advanced Editor, there is a property called FileNameColumnName. This property is used to add a column to the flat file source where the File Name is added.

    enter image description here

    You should only write the value of the column name and it will be added to the flat file source.

    enter image description here

    (2) Using a Derived Column Transformation

    Your issue can be solved by adding a Derived Column Transformation within the Data Flow Task. Then, add a column to the data pipeline using the variable that contains the File Name. (The variable used in the ForEach Loop Container's variables mappings tab)

    You can learn more about Derived Column Transformation in the following article:

    Similar questions: