How to loop the folder's content in SSIS. In 10 easy-to-follow steps

First, drag the Data Flow Task to Foreach Loop Container:

Then press right click on Foreach Loop Container, select Edit..., select Variable Mappings; then on Select the variables to map to the collection value, click the Variable's cell, select <New Variable...>

Then on Name textbox, type this: Fullfilename:

Click OK, then select Collection(on left navigation, between General and  Variable Mappings), then click the ellipsis button [...] of Expressions, then on Property's cell, click the dropdown then select FileNameRetrieval, then click the ellipsis button [...] of Expression's cell

Then drag the [User::Filename] to Expression's text area

Then click OK two times to go back to Foreach Loop editor. If the Enumerator configuration don't appear as follows, trick Business Intelligent Development Studio, on Enumerator, select any option, then select back the Foreach File Enumerator. Then specify the source Folder of text files, you can click the Browse button to do this. On Files, enter the wildcard, it could be *.csv or *.txt, depending on the file extension that was given to text file. Then click OK

Then go to Data Flow, select the text file connection from the bottom:

Then select, the Expression property:

Then on Property Expressions Editor, select the ConnectionString from Property's cell dropdown

Then click the Expression's ellipsis button, then drag the @[User::Filename] (from the Variables list) to Expression's text area, then the Property Expressions Editor should appear as follows:

Click OK until you get back to Data Flow tab. Click the right green arrow button (shortcut key: F5)

That's it!

Happy transforming! :-)


Video how-to: