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

Melissa Connors

Published On: November 12, 2019

Categories: SSIS 0

In my previous DTS xChange post, I answered a question regarding SSIS packages using 32-bit drivers and 64-bit machines. In this post, I am going to address another common question related to using SSIS for looping through and loading files in order to move them to an archive folder.

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

Processing files in a specific folder in DTS takes a good amount of work. This same process in SSIS is much easier. Use the Foreach Loop Container in SSIS to loop through files in a specified folder. The following sample SSIS Package shows you how to process each file (Nightly_*.txt) in C:\SSIS\NightlyData. After each file is processed it's moved to the Archive folder.

Migrate Loop Example

Installing the Sample Package

Complete the following steps to setup the test Sample SSIS Package:

  1. Download and Extract the For_Each_Loop_File_Test.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 the provided sample files to C:\SSIS\NightlyData.
  6. Open FAQTest.sln and analyze or run the Sample Package to test it.

 

Step-By-Step

Complete the following step by step instructions for this sample:

 

Define Variables

The sample package uses five variables with the following settings: 

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]

Note:  The Is Expression column refers to the EvaluateAsExpression property of SSIS variable.

 

Define Connections

The sample package requires two connections.

  1. Create an oledb connection ((local).tempdb) to use tempdb database.
  2. Create a Flat file connection (NightlyDataFile) for any available file in the SourceFolder (in this example use C:\SSIS\NightlyData\Nightly_01.txt).
  3. Configure the Flatfile ConnectionString Proptery as an expression to make the FlatFile connection dynamic.
    Property Expressions Editor Screenshot

Note:  The FlatFile connection needs to be Dynamic so that one connection can load several files from the source folder. The varFilePath variable will be updated on each iteration of the file in the Foreach Loop container.

 

Execute SQL Task (Create or Truncate Table)

Create an Execute SQL task to execute the following SQL Statement:

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)
)
endtruncate table tempdb.dbo.Staging_Customers

Note:  Make sure you select the tempdb connection for this task.

 

Foreach Loop Container (Load Nightly Data Files)

Add the Foreach Loop Container and double click it to open the properties dialog box. Apply the following settings:

Migrate Loop Select Enumerator Example

 

Select Variable Map

 

 

Data Flow (Load Data File)

Add the Data Flow inside the Foreach Loop Container by completing the following steps:

1. Double click the dataflow, and add the FlatFile Source.

2. Add the OLEDB Destination. Connect the FlatFile Source and the OLEDB Destination.

Migrate Loop Example Screenshot

3. Double click on the OLEDB Destination to select tempdb connection, then select mapping and configuration.
Data Flow Mappings Example

 

File System Task (Move File to Archive)

Place the File System Task to move the processed file to the archive folder. Specify the properties as displayed below:

Loop Test Connections Example

 

Connect All Task and Testing

Connect all tasks and run the package. If everything is configured correctly, the files should load into the staging table and then move to the Archived folder.

Migrate Loop Example

Your task is complete.

About DTS xChange

DTS xChange provides automated conversion of SQL Server 2000 DTS packages to SSIS 2005 and SSIS 2008. To learn more about its full features and how you can migrate to SSIS in minutes, see the DTS xChange product page.

Melissa (@MelikaNoKaOi) is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.


Comments