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
Search and replace in files using Simple text search
In this option you can specify text you want to search and text you want to replace with. You can check ignore text if you dont want case-sensitive search/replace.
Extended text search (special characters allowed)
For extended search mode you may use any of the following in your search/replace string
- \n – for line feed
- \r – for return character
- \0 – for null character
- \t – for tab character
- \v – for vertical tab character
Example:
Search: “Hello\r\nWorld”
Replace: “Hello World”
Regular expression search/replace
Search and Replace File Content in SSIS (Find sub string, Search for special character)
Advanced File System Task
If you want to perform some other operations then here is the list of few features supported by Advanced File System Task.
- 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 2005, 2008, 2012, 2014, 2016 (32 bit and 64 bit)
Download FREE SSIS Components
Conclusion
Search and replace in many files can be tricky but using Advanced File System Task it can be few click. Click here to download.
