Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSIS For Each Container - File Enumerator

SSIS For Each Container 2012

This article demonstrates how to automate archival of files by using an SSIS job with a For Each Container that will use a File Enumerator and a File System Task to interate through files on a Working Directory and copy them to a uniquely named Archive directory nightly as a scheduled job or manually, depending on the need of the company or party using it. The Archive Name convention uses SSIS Syntax to create a directory with a 2nd File System Task with a folder named using an Expression in a Variable name for a new Folder for the files to be copied into.

Create the Working Folder with Files

Create the folder with the working files that get modified on a daily basis that you need to archive. I used C:\WorkingFiles\ as my folder.

Working Folder with Files

Create Variables for the folder and the files to copy

Right click in the Control area and select variables. Create 2 variables - one for the files to copy and one for the folder to create (based on the date) and the one to copy to. We will be creating an Expression to get the current Date and convert it into a String for a new file folder name for each Archived file.

SSIS Variables

Expression for the new folder name

Syntax for the new folder name is a little tricky for SSIS. I need a string name for my new folder each night, so I need to do a data conversion\cast with a DT_WSTR cast and parse the current date. I will use the RIGHT syntax to parse the date into my new File Folder to store the files. The DT_WSTR string cast allows the Date Time variable to be appended to the folder path as a unique location for backups for that day..

SSIS BackUp Location

Add a new Foreach Loop Container to the Control Flow

Drag and drop a Foreach Loop Container into the Control Flow area of the Package. Right click on it to Edit the details.

SSIS ForEach Loop

Edit the Foreach Loop Properties

cClick on Collection, and make sure the Enumerator property is set to Foreac File Enumerator.

SSIS ForeachLoop Properties

Click on the Variable Mappings in the left hand box and select the variable User::strFileName from the drop down. Leave the 0 Index default. Click OK to finish.

SSIS Variable Mappings

Add and Configure a File System Task for the Foreach Loop Container

Drag and drop a File System Task directly onto the Foreach Loop Container. Right click on the task and select Edit.

SSIS FileSystem Task

Make sure the the Operation is set to Copy File. Click in the drop downs for IsDestinationPathVariable and then IsSourcePathVariable and change each one to True. Select the DestinationVariable and select the User::strFolderName as the location for the files to go. Select the SourceVariable as the User::strFileName variable. Click OK to finish once completed.

SSIS Copy File operation

File System Task to Create Folder

Add one more File System Task to the Control Flow section of the Package to create the directory prior to the copy task in the Foreach Loop. Right click on it and select Edit.

SSIS File System Task creating folder

Select the Operation as Create directory from the drop down. Select IsSourcePathVariable = True and select the User::strFolderName as the SourceVariable. This task will create the archive folder with the proper name (the date). The folder must be created prior to the execution of the Foreach Loop or an error will be thrown - the loop does not have the ability to test for the existence of the destination location prior to executing the Copy File task.

SSIS Create Directory

Execute The Foreach Loop Container Package

Click on the Green Arrow in the Toolbar or F5 to start Debugging. Notice that the Green Check marks indicate that the steps have executed as expected.

SSIS Foreach Loop

Check the C:\ArchiveFolder for the new folder created with the current date and verify that the files have been copied.

Verify Archive Folder