FAQ - How to loop through files in a specified folder, load one by one and move to archive folder using SSIS
Sample Package Installation
Perform the following steps to setup and test the above sample SSIS Package
1. Extract the sample zip file
2. Create a folder C:\SSIS
3. Create a Folder C:\SSIS\NightlyData
4. Create a Folder C:\SSIS\NightlyData\Archived
5. Copy Provided sample files to C:\SSIS\NightlyData
6. Open FAQTest.sln and analyze or run the Sample Package to test
|Define Variables : The sample
package uses five variables with the following settings. "Is Expression"
column refers to the EvaluateAsExpression property of SSIS
|Define Connections : The sample
package requires two connections. |
- Create an oledb connection ((local).tempdb) to use tempdb database.
- Create a Flat file connection (NightlyDataFile) for any available file in the SourceFolder (in this example use C:\SSIS\NightlyData\Nightly_01.txt). We have to make FlatFile connection Dynamic so we can use one connection to load several files from the source folder. In order to make FlatFile connection dynamic we have to configure ConnectionString Property as an Expression (See below screenshot). varFilePath will be updated on each iteration of file in Foreach Loop Container (Check the Foreach Loop Container description below in this article).
|Execute SQL Task (Create or Truncate Table)
: Next step is to create an execute sql task to execute the
following SQL Statement also make sure you select tempdb connection for
this task. |
|Foreach Loop Container (Load Nighly Data Files) : Place Foreach Loop Container and double click to open properties dialog box. Apply the following settings displayed in the screenshots.|
Fig-1 (Select Enumerator, Folder, Files and Retrieve
|Data flow (Load Data File) : Place
Data flow inside the Foreach Loop Container. |
- Double click the dataflow
- Place FlatFile Source
- Place OLEDB Destination.
- Connect FlatFile Source and OLEDB Destination
- Double click on the OLEDB Destination to Select tempdb connection. Then click on mapping and configu
|File System Task (Move File to Archive) : Last step is to place File System Task to move processed file to archive folder. Please specify the properties displayed as below.|
|Connect all tasks and testing : Connect all tasks as shown in the very first screen and run the package. If everything is configured correctly then files should load into staging table and then moved to Archived folder.|