Get list of files in SSIS for Looping

Introduction

In this post you will learn how to use Advanced File System Task to get list of files and folders into variable. Advanced File System Task is significantly better than native SSIS File System Task

How to get list of files in SSIS

Most simplest way to get list of files in SSIS is use wildcard pattern as below screenshot. You can also use Scan Recursive option to include files inside child folders. For advanced filtering options use Filter and Sorting Tab

1. Download and install SSIS PowerPack from here
2. From toolbox of SSIS designer drag ZS Advanced File System Task
3. Double click Advanced File System task to configure it
4. Select Action as [Get file list as ADO.net DataTable]
5. Now select files you want to get using wildcard pattern (e.g. c:\ssis\file*.txt)
6. In the result variable select variable or create new variable with Object datatype. This variable will host file list in the form of ADO.net DataTable

SSIS Advanced File System Task – get file list as ADO.net Recordset into variable

Loop through files in SSIS using ForEach Loop Task (use ADO.net Recordset variable)

Variable Mappings for ForEach Loop Task – Loop through files in a folder using SSIS

Get files modified in last N days

To get files modified in last N days you can use Advanced File System Task.

1. Download and install SSIS PowerPack from here
2. From toolbox of SSIS designer drag ZS Advanced File System Task
3. Double click Advanced File System task to configure it
4. Select Action as [Get file list as ADO.net DataTable]
5. Now select files you want to get using wildcard pattern (e.g. c:\ssis\file*.txt)
6. Go to Filter and Sorting tab. In the Where Field enter expression LastEditInDays < 5 This will give you list of files modified in last 5 days. Here are some more examples.
   
   C#

   ////////////////////////////// Examples: ////////////////////////////// AgeInDays < 5 //Get files created in last 5 days LastEditInDays > 3 //Get files modified before 3 days AgeInDays > 10 AND LastEditInDays < 3 //Get files created before 10 days and modified in last 3 days Size < 1024 //Get files where File size is less than 1024 bytes CreationDate BETWEEN #2015-01-01# AND #2015-01-01# Name IN ('File1.txt', 'Download.msi') FullPath LIKE 'c:\temp\da*') Extension IN ('.txt','.csv') LastModifiedDate > #2013-01-01T16:00:00# IsReadOnly = True ///////////////////////////////////////// //Supported Columns for expression: ///////////////////////////////////////// Name, FullPath, Extension, FolderPath, AgeInDays, LastEditInDays, Size, CreationDate, CreationDateUtc, LastModifiedDate, LastModifiedDateUtc, IsReadOnly

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   ////////////////////////////// Examples: ////////////////////////////// AgeInDays < 5  //Get files created in last 5 days LastEditInDays > 3 //Get files modified before 3 days AgeInDays > 10 AND LastEditInDays < 3  //Get files created before 10 days and modified in last 3 days Size < 1024  //Get files where File size is less than 1024 bytes CreationDate BETWEEN #2015-01-01# AND #2015-01-01#   Name IN ('File1.txt', 'Download.msi') FullPath LIKE 'c:\temp\da*') Extension IN ('.txt','.csv') LastModifiedDate > #2013-01-01T16:00:00# IsReadOnly = True ///////////////////////////////////////// //Supported Columns for expression: ///////////////////////////////////////// Name, FullPath, Extension, FolderPath, AgeInDays, LastEditInDays, Size, CreationDate, CreationDateUtc, LastModifiedDate, LastModifiedDateUtc, IsReadOnly

   

   SSIS Advanced File System Task – Get files modified in last N days

Get files with specific size

To get files with specific size you can use Where Expression on Size attribute (Size is in Bytes)

1. Download and install SSIS PowerPack from here
2. From toolbox of SSIS designer drag ZS Advanced File System Task
3. Double click Advanced File System task to configure it
4. Select Action as [Get file list as ADO.net DataTable]
5. Now select files you want to get using wildcard pattern (e.g. c:\ssis\file*.txt)
6. Go to Filter and Sorting tab. In the Where Field enter expression Size < 1024 This will give you list of files which are less than 1024 bytes (e.g 1KB).
   
   C#

   Size < 1024

   1	Size < 1024

Get files with Include/Exclude pattern (Use Regular Expression)

Sometimes you have need to list files with specific name patterns which is hard to get using simple wildcard then use Advanced Regular Expression option. For this use wildcard in Path (e.g. c:\ssis\*.*) and then in the Filter and Sorting tab use Include / Exclude Regular Expressions (e.g. RegX) for file name pattern.

For Example if you want to include all files containing *MMDD.* pattern but not *.zip file or *.gz then use below expressions

How to get list of folders in SSIS (list directories)

Coming soon

Loop through files or folders in SSIS using ForEachLoop Task

Coming soon

Read Recordset and load into database (e.g. SQL Server Table)

Coming soon

Conclusion

Microsoft native file system task has very limited features and many scenarios require you to write C# / VB.net Script. But using ZS Advanced File System Task you can eliminate need for scripting and use simple drag and drop approach. To use this task Download SSIS PowerPack