FAQ - How to loop through files in a specified folder, load one by one and move to archive folder using SSIS

In DTS if you want to process files in a specified folder then you have to do good amount of work. But If you want to implement same in SSIS then you don't have to do much work. You can use "Foreach Loop Container" in SSIS to loop through files in a specified folder.

The following sample SSIS Package shows how to process each file (Nightly_*.txt) in "C:\SSIS\NightlyData". After each file is processed its moved to Archive folder.

 Click here to Download sample package


DTS to SSIS Migrations/Conversion
 

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

Step-By-Step

Define Variables : The sample package uses five variables with the following settings. "Is Expression" column refers to the  EvaluateAsExpression property of SSIS variable.
 
Variable Name Data Type Value Is Expression Expression
varSourceFolder String C:\SSIS\NightlyData False  
varArchiveFolder String   True @[User::varSourceFolder] + "\\Archived"
varArchivePath String   True @[User::varArchiveFolder] + "\\" + @[User::varFileName]
varFileName String   False  
varFilePath String   True @[User::varSourceFolder] + "\\" + @[User::varFileName]

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).
 

DTS to SSIS Migrations/Conversion
 

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.

SQL
if object_id('tempdb.dbo.Staging_Customers') is null
begin
create table tempdb.dbo.Staging_Customers
(
 FilePath varchar(255) 
,CustName varchar(255) 
,CustPhone varchar(255) 
,CustEmail varchar(255) 
)
end

truncate table tempdb.dbo.Staging_Customers

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 filename)
DTS to SSIS Migrations/Conversion

Fig-2 (Select Variable Mappings)
DTS to SSIS Migrations/Conversion
 

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

Source->Destination
DTS to SSIS Migrations/Conversion

Mappings
DTS to SSIS Migrations/Conversion
 

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.

DTS to SSIS Migrations/Conversion
 

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.
 

Copyright 2009 Pragmatic Works Inc.