The ZS Advanced File System Task WHERE condition allows you to filter files and folders based on name, size, date, path, and attributes before processing them in SSIS.
Many users look for practical WHERE condition examples for scenarios such as file cleanup, incremental loads, archiving, and report processing. This article provides real-world, copy-paste-ready examples to help you get started quickly.
What Is the WHERE Condition in ZS Advanced File System Task?
The WHERE condition works similarly to a SQL filter and lets you control which files or folders are selected before performing operations such as Copy, Move, Delete, or Load.
Important Notes Before Using WHERE Conditions
-
Size is measured in Bytes
-
DateTime values must be enclosed within
# # -
Supported DateTime formats:
#yyyy-MM-dd##yyyy-MM-dd HH:mm:ss#- For the
=operator, 7-digit milliseconds are required
-
Wildcard character:
*
Basic File Filtering Examples
Select files older than 5 days:
AgeInDays > 5
Exclude recently edited files:
LastEditInDays <> 0
Process files smaller than 1 MB:
Size < 1024000
Select read-only files:
IsReadOnly = True
File Name Filtering Examples
Filter specific file names:
Name IN ('File1.txt', 'Download.msi')
Files starting with a specific prefix:
Name LIKE 'chat%'
Name LIKE 'ticket%'
Exclude temporary files:
Name NOT LIKE 'temp*'
File Extension Filtering Examples
Process only CSV and TXT files:
Extension IN ('.csv', '.txt')
Exclude executable files:
Extension NOT IN ('.exe', '.dll')
Folder Path and Full Path Filtering
Files inside a Reports folder:
FolderPath LIKE '*/Reports/*'
Exclude Archive folders:
FolderPath NOT LIKE '*/Archive/*'
Filter using full path:
FullPath LIKE 'c:\temp\da*'
Date Filtering Using Absolute Dates
Files modified after a specific date:
LastModifiedDate > #2025-01-31#
Exact timestamp comparison (requires 7-digit milliseconds):
LastModifiedDate = #2025-01-31 23:59:59.1234567#
Files modified within a date range:
LastModifiedDate >= #2025-01-31#
AND LastModifiedDate <= #2025-02-28#
Relative Date Filtering (Dynamic & Incremental Loads)
Files modified in the last 7 days:
LastModifiedDate > #<<today-7d,FUN_TO_DATETIME>>#
Files modified since yesterday:
LastModifiedDate > #<<yesterday+24h,FUN_TO_DATETIME>>#
Files modified since start of the month:
LastModifiedDate > #<<monthstart,FUN_TO_DATETIME>>#
Using SSIS Variables in WHERE Conditions
Incremental load using last run date:
LastModifiedDate >
#{{User::LastRunDate,yyyy-MM-dd HH:mm:ss.fff}}#
Add 2 months to a variable-based date:
LastModifiedDate >
#<<+2month||yyy-MM-dd HH:mm:ss.fff||{{User::MyOrderDate}},FUN_TO_DATETIME>>#
Real-World Use Cases
Delete Files Older Than 30 Days
AgeInDays > 30
Archive Large Files Older Than 7 Days
Size > 52428800
AND AgeInDays > 7
Process Today’s CSV Reports
Extension = '.csv'
AND LastModifiedDate >= #<<today,FUN_TO_DATETIME>>#
Combining Multiple WHERE Conditions
Process text files with specific prefixes:
(Name LIKE 'chat%' OR Name LIKE 'ticket%')
AND Extension = '.txt'
Monthly reports excluding archived files:
Extension IN ('.csv', '.xlsx')
AND FolderPath LIKE '*/Reports/*'
AND FolderPath NOT LIKE '*/Archive/*'
AND LastModifiedDate >= #<<monthstart,FUN_TO_DATETIME>>#
Supported Columns in WHERE Clause
Name
FullPath
Extension
FolderPath
AgeInDays
LastEditInDays
Size
CreationDate
CreationDateUtc
LastModifiedDate
LastModifiedDateUtc
IsReadOnly
Learn More
To understand placeholder functions and dynamic expressions, refer to:
https://zappysys.com/links/?id=10014
Conclusion
The ZS Advanced File System Task WHERE condition gives you complete control over file selection in SSIS. By combining file attributes, dates, paths, and SSIS variables, you can easily build incremental loads, automated cleanup jobs, and file archival workflows.
If you have a specific scenario, feel free to post it here in ZappySys Community — we’re happy to help 