Sometimes you may need to make an advanced filtering on file path, file date, size etc in SSIS Amazon S3 Task or SSIS Azure Blob Storage Task . For example, you have a directory “Customers“, which contains client directories. Each of them contains a directory named “Invoices“, which is partitioned by year. E.g. “Customers/Microsoft/Invoices/2015”. So if you want to get all invoices of all customers of all years, use Advanced Filter & Sorting option and configure Where clause to use wildcards (*) like this:
PrerequisitesBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
Using Advanced Search – Regex / SQL Expression (Filter by file date, size, name)
Sometimes it’s necessary to filter file based on advanced attributes such as file Age (How old), File Size, Multiple File Patterns (e.g. Delete *.zip, *.gz, *.rar in one action)
You can use Advanced Tab to filter further. First supply primary filter on Source Tab (e.g. mybucket/*.* ) and then supply your advanced filter (e.g. you can exclude *.exe and *.msi file from your delete list as below screenshot, also only delete files between 1000 and 50000 bytes in file size).
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 'mybucket\somefolder\month_*')
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
Select Amazon S3 files older than X days
If you like to select / delete files older than certain days then use below technique.
AgeInDays > 10
- First Specify primary search criteria on Source tab
- Go to Advanced Filter Tab and specify Following Pattern in Where Textbox (For example delete Files older than 10 days )