Looping through Files Within a Directory Using SSIS

November 4, 2022
|
SSIS

A common scenario with performing file processing tasks, is looping through a set of files within a directory and storing them in an archive or a staging area to await further processing. For example, you may want to transmit them via SFTP or use them to populate a database.  I will be describing how to do the following with SSIS.

• Create custom archive directory, if do not already have one.

• Populating the archive directory with files that meet a specific naming criterion.

• Copy and rename the files, into a staging directory, awaiting further processing.

‍

We will be accomplishing our objective by using three SSIS tasks and a Foreach Loop container.  The first task will create the archive directory. The second task will move all the files meeting our criterion, from the source directory, to the archive directory.  The final task will copy and rename the files into the staging directory.  In a real-world situation, I would create a package and/or tasks that would create the folder structures, if they did not already exist.  I will provide an example on how to create the custom archive directory using a file system task.  However, you will need to create the following directories for this tutorial. You can download the test files or you could create your own.

1.C:\Outbound

2.C:\Test Files

‍

Package Variables

Create a project in SSIS.  Next create and populate the following package variables.  

Name Datatype Value

1.vArchiveDirectory String Expression, see below.

2.vFullArchiveDirectory String Expression, see below.

3.vFullOutboundPath String Expression, see below.

4.vOutboundPath String c:\OutBound\

5.vSourceFileName String testfile1.txt

6.vSourceFilePath String C:\Test Files\

7.vSourceFullFilePath String Expression, see below.

Package Variable Descriptions and Sequence of Variable Creation

Create the variables in the sequence they are listed.  Some variables have dependencies on other variables.

1.vArchiveDirectory – We will populate this variable through an expression.  The value of the variable will allow SSIS to create the directory structure C:\Archive\YYYY\DDMMYY,  or use the directory if it already exists.  We will populate this variable with the following expression.

"c:\\Archive\\"+(DT_WSTR,4)YEAR(GETDATE())+"\\"+(LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+"\\"

2.vSourceFileName – This variable will provide the name of the source file. It is testfile1.txt for our example.

3.vFullArchiveDirectoryPath – This variable will provide the absolute path.  We will populate this variable with the following expression, @[User::vArchiveDirectory]+ @[User::vSourceFileName]

4.vOutboundPath- This variable will provide the path to the staging area, c:\OutBound\.

5.vFullOutboundPath – This variable will provide the absolute path with the new file name.  We will populate this variable with the following expression,

@[User::vOutboundPath]+ SUBSTRING( @[User::vSourceFileName] , 1 , FINDSTRING( @[User::vSourceFileName],".",1) - 1 ) + "_" + (LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+ SUBSTRING( @[User::vSourceFileName] , FINDSTRING( @[User::vSourceFileName],".",1) , LEN( @[User::vSourceFileName] ) )

6.vSourceFilePath – This variable provides the path to our source directory, C:\Test Files\.

7.vSourceFullFilePath – This variable will provide the absolute path to the file in the source directory.  We will populate the variable through the following expression, @[User::vSourceFilePath]+ @[User::vSourceFileName]

Creating the Package Tasks

Drag a file system task onto the canvas, configure the task so that it matches the image. We will be using this task to create the “Archive” directory.  Note, we are informing SSIS to use the directory structure if it already exists.  Click “OK” once configuration is complete.

Next, drag a Foreach Loop Container onto the canvass, and configure it so that it matches the image.  We are identifying the folder, the loop should search, as “C:\Test Files”. Additionally, we are identifying the file criterion as any text file that begins with “testfile” followed by the wildcard “*” and ends with “.txt”.  Check the “Name and extension” radio button under the “Retrieve file name” option.  Click “OK”.

Next, configure the Variable Mappings. Click on “OK” when complete.  Your Foreach Loop Container is now configured.

Connect the task that creates the Archive directory to the Foreach Loop container, using a precedence constraint. Your package should resemble the following image.

Drag a file system task onto the Foreach Loop Container and configure it so it matches the following image.

1.Set the operation to “Move”, this will allow you to configure the appropriate values.

2.Set IsDestinationPathVarabile to “True”.  

3.Set DestinationVariable  to your archive directory,  using the variable, User::vArchiveDirectory.

4.In this example, we are determining that SSIS cannot overwrite the files, if they already exist. Set OverwriteDestination to “True” if you wish to overwrite the files if they exist.

5.Set is SourcePathVariable to “True”.  

6.Set the SourceVariable value to User::vSourceFullFilePath.  The variable will provide the absolute path to the source of the files.

7.Click “OK” when complete.  You have configured the task to move the files from the source to the archive directory.

Drag another File System task onto the Foreach Loop Container and configure it so that it matches the following image.

1.Set the operation to “Copy”, this will allow you to configure the appropriate values.  The task will do two tasks in one.  It will copy to the staging directory and rename it in one task.

2.Set IsDestinationPathVarabile to “True”.  

3.Set DestinationVariable to your staging directory, using the variable, User::vFullOutboundPath.

4.In this example, we are determining that SSIS cannot overwrite the files, if they already exist. Set OverwriteDestination to “True” if you wish to overwrite the files if they exist.

5.Set is SourcePathVariable to “True”.  

6.Set the SourceVariable value to User::vFullArchiveDirectory.  The variable will provide the absolute path to the archive directory containing the files you wish to rename and stage for further processing.

7.Click on “OK” when complete. You have configured the task to copy and rename the files from the archive directory to the staging directory.

Connect the first file task in the Foreach Loop Container to the second file task.  Your Foreach Loop Container should resemble the following image.

Before executing the package, note the files that exist in our source directory.  All the files do not meet our criterion. We are looking for files that are named testfilex.txt.  Some of the files are Excel files and others do not have a meet the criterion for various reasons.

Execute the package.  After execution, the following files remain in our source directory since they did not meet the criterion.

Note the files in our Archive directory.  A folder was created for the year, containing a subfolder for the day the package was executed.

The staging directory, c:\Outbound, has also been populated with copies of the files, with new filenames.

I hope you found this tutorial helpful.

‍

← View all posts