Advanced SSIS File System Task

Advanced SSIS File System Task is designed to full fill most common file system related operations in SSIS. Native SSIS File System Task provided by Microsoft lakes many useful features (e.g. copy/move/delete/rename multiple files, search/replace etc).

Features

File operations for single or multiple files using wildcard(e.g. copy, move, rename, delete, write to file, get properties)
Folder operations such as delete folder and create folder
Support for bulk rename (e.g. by specifying {%name%}.{%ext%}.old as new name will rename all selected files to *.old)
Search and Replace in files (Regular expression search supported)
Change file encoding
Get file properties as data table which you can loop easily (Using ForEach Loop – ADO Enumerator)
Option to continue on error (e.g. Copy file only when source file found)
Auto create missing target directory
Use placeholders anywhere in the path, makes it easy to timestamp folders or files (e.g. c:\Archive\{{System::StartTime,yyyMMdd}})
Regular Expression based Include Pattern (e.g. ^(file\d\d).(\w+)$ will include file01.exe,file22.csv …)
Regular Expression based Exclude Pattern (e.g. (.exe|.msi) will exclude files with extension exe or msi)
Sort based on specific property (e.g. Order Size DESC – extract top 10 files by size)
Advanced SQL like filter support (e.g. Size> 1024 AND AgeInDays > 10 AND Extension IN(‘.txt’,’.csv’) will include file greater than 1024 bytes and extension is txt or csv)
Support for Regular Expression based Include Pattern (e.g. ^(Customer\d\d).(\w+)$ )
Support for SQL Server 2022, 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory

In Visual Studio, drag and drop the Advanced File System Task in the design surface from SSIS Toolbox.

In Visual Studio, drag and drop the Advanced File System Task in the design surface from SSIS Toolbox.

How to Copy Multiple Files (Use wildcard)

Advanced SSIS File System Task – SSIS Copy Multiple Files (Use wildcard)

How to Delete Multiple Files (Use wildcard)

Advanced File System Task How to Delete Multiple Files

Advanced SSIS File System Task – SSIS Delete Multiple Files (Use wildcard)

How to Delete files older than X days – Advanced Sorting and Filtering

Advanced SSIS File System Task - Advanced Sorting and Filtering

Advanced SSIS File System Task – SSIS Delete files older than X days and size greater than Y

How to Rename Multiple files (Use wildcard pattern)

Advanced SSIS File System Task - How to Rename Multiple files

Advanced SSIS File System Task – SSIS Rename Multiple files (Use wildcard pattern)

How to Search and Replace multiple files (Extended mode and Regular Expression Support)

Advanced SSIS File System Task - How to Search and Replace multiple files

Advanced SSIS File System Task – SSIS Search and Replace multiple files (Use wildcard pattern)

How to check file exists (check for single or multiple files)

Advanced File System Task How to check file exists

Advanced SSIS File System Task – SSIS check file exists (check for single or multiple files)

How to change file encoding (to UTF8, UTF16 or ASCII)

Advanced SSIS File System Task - How to change file encoding

Advanced SSIS File System Task – SSIS change file encoding (to UTF8, UTF16 [UNICODE] or ASCII) (single or multiple files)

How to get sorted file list and loop through it

Advanced SSIS File System Task – SSIS get sorted file list and loop through it

How to create new file and write text to it

Advanced SSIS File System Task - create new file

Advanced SSIS File System Task – SSIS create new file and write text to it

Articles / Tutorials

Click here to see all articles for [SSIS Advanced File System Task] category

SSIS PGP Encryption / Decryption (Using FREE GPG Tool)

Introduction In this new article, we will show you how to perform PGP encryption using SSIS (encrypt / decrypt files using public / private key). Our previous article was aboutÂ SFTP using our SFTP taskÂ for SSIS. Now we will show how to encrypt the information. Requirements First of all, you will need SSDT forÂ Business Intelligence for […]

Calling SSRS Reports in SSIS (Export / Email)

Introduction about calling SSRS Reports in SSIS Calling SSRS Reports in SSIS is straightforward using the ZappySys Reporting Services task. Also, we will show how to export files SSRS files using SSIS, howÂ to send reports in emails and how to send parameters. In addition, we will be using thisÂ ZappySys SSIS PowerPackÂ component to make things work: […]

How to read and write data to HTML in SSIS

Introduction to read and write data to HTML in SSIS In this article, we will show how to send values from an SSIS Variable to an HTML file. We will use the SSIS Advanced File System Task to store the list of system files of a folder into a variable and then we will use […]

Using Regular Expressions in SSIS

Introduction In this short article you will learn how to write Regular expressions in SSIS (i.e. Regex) and what tool to use to test them. You will also find helpful resources on how to write more sophisticated expressions and learn more about them. For demo purpose we will use FREEÂ SSIS Regex Parser TaskÂ to parse and […]

How to remove invalid characters from XML using SSIS and Regex

Â Introduction In this blog post you will see how to remove invalid characters from XML using SSIS. We will use search and replace feature of Advanced File System Task. Remove Invalid charactersÂ from XML Xml file specification have restriction about which characters can be part of XML data and which should be avoided. If you use […]

SSIS check file is locked and wait until file is unlocked (C# Script)

Introduction In this small blog post you will learn How to move files using SSIS Advanced File System Task and How to wait until file is unlocked using C# Script Task. How to check if file is locked (SSIS C# Script Task) If you want to check if file is locked in C# then below […]

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 […]

Search and replace in files using SSIS – No Coding

Introduction Many times you have requirement to search and replace in files (single or multiple file) content using SSIS. If you are not C# or VB.net programmer then you may find yourself at dead end struggling how to achieve this in SSIS. If you have this need then you can use Advanced File System Task […]

How to change file encoding in SSIS (UTF8, ASCII or UTF16)

Introduction Many times during your ETL process you receive files which are in different encoding than you expect. Example all files are in UTF-16 format but your application expect them to be in UTF-8. Or Sometimes files are in ASCII format and you want to convert to UTF-8. In this post you will see how […]