SSIS ForEach File loop - insert file name to table

Question

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

This is demonstrated in any number of examples online. Please try one and post back a specific issue. Like this one sqlservercentral.com/articles/Integration+Services+(SSIS)/67871 — Nick.Mc
– Nick.Mc, Commented Feb 5, 2019 at 0:14

skadam85 · Accepted Answer · 2019-02-05 00:05:35Z

3

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!

answered Feb 5, 2019 at 0:05

skadam85

55310 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

skadam85 Over a year ago

Glad it helped.

Hadi Over a year ago

@Dominic you have to accept this answer if it solved the issue. Check the Tour page

J Weezy · Accepted Answer · 2019-02-04 23:24:30Z

1

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.

answered Feb 4, 2019 at 23:24

J Weezy

4,0695 gold badges44 silver badges102 bronze badges

Collectives™ on Stack Overflow

SSIS ForEach File loop - insert file name to table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related