CODEWITHSUNDEEP
sql-serverssis
Dominic
Dominic

Reputation: 175

SSIS ForEach File loop - insert file name to table

I'm building a SSIS package (using VS 2017) to load a bunch of CSV files from a specific folder. It works great using the ForEach File loop. Data Flow task has a Flat File Source and OLE DB Destination. I want to be able to save the filename in the same table, along the data from the CSV file. How can I do this??

thanks a lot for your time and help

Upvotes: 3

Views: 5854

Answers (2)

skadam85
skadam85

Reputation: 543

One other method if you want to load the entire file path of the flat files is

  • Right click on Flat File Source in Data Flow tab
  • Click "Show Advanced Editor"
  • Click "Component Properties"
  • Under Custom Properties you will find "FileNameColumnName".
  • If you give a name (e.g.: FlatFileName) then that will appear as one of the source output columns in mapping that contains the file name value(full path with file name). It can be mapped to any available column in destination or modified using Derived column task to get only file name.

In my development, I have stored mostly the entire path which helped me in better tracking.

One other method for anyone who comes across this question, instead of using ForEach loop you can also use a simpler method

  • Right click in Connection Manager
  • Click on "New Connection"
  • Select "MULTIFLATFILE" connection type and click Add.
  • In Connection manager editor, insert location and use wildcard * (E.g.: \\ABC\XYZ\file_*.txt) to pick all the flat files in that folder.
  • This automatically loops through all the flat files in that folder.

Hope this helps!

Upvotes: 3

J Weezy
J Weezy

Reputation: 3957

The ForEach File Enumerator can capture the file name and assign it to a variable, which can then be routed to the connection string variable of a connection manager for dynamic loading. This same methodology can be applied to write the file name to a database table.

In the data flow, add a Derived Column transformation and add a new column called 'FileName' (or whatever) and then set it's value to the variable value that the ForEach File Enumerator is setting for the file name.

Upvotes: 1

Related Questions