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.
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.
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..
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.
Edit the Foreach Loop Properties
cClick on Collection, and make sure the Enumerator property is set to Foreac File Enumerator.
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.
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.
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.
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.
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.
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.
Check the C:\ArchiveFolder for the new folder created with the current date and verify that the files have been copied.
