|
Advanced SSIS File System Task
|
 
|
|
Advanced SSIS File System Task is designed to fulfill most common file system-related operations in SSIS. The native SSIS File System Task provided by Microsoft lacks many useful features, such as copying/moving/deleting/renaming multiple files, and searching/replacing, etc.
|
Note: If you want to operation with multiple files then use wildcard pattern as below (when you use wildcard pattern in source path then system will treat target path as folder regardless you end with slash ). Examples: c:\data\file123.csv (single file). c:\data\file*.csv (all files starting with file). c:\data\subfolder\*.csv (all files with .csv Extension and located under folder subfolder).Set Target Path with Date and Time and file name with suitable extension.
| Property Name | Description | ||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Action | Specifies what action you want to perform
|
||||||||||||||||||||||||||||||||||||||||||||||||
| Permission | Specifies permission for file
|
||||||||||||||||||||||||||||||||||||||||||||||||
| SearchString | String or pattern you want to search in file content | ||||||||||||||||||||||||||||||||||||||||||||||||
| ReplaceString | New string you want to replace with | ||||||||||||||||||||||||||||||||||||||||||||||||
| Encoding | Encoding for target file (Only valid for Create new file, Change Encoding, Search Replace options)
|
||||||||||||||||||||||||||||||||||||||||||||||||
| SearchMode | Search mode (e.g. Normal, Extended or Regular Expression). In extended mode search/replace you can specify whitespace characters such as \r or carriage return, \n for new line, \t for tab, \0 for NULL and \v for vertical tab. In Regular expression search you can spe
|
||||||||||||||||||||||||||||||||||||||||||||||||
| SearchIgnoreCase | By default search is case sensitive. Check this option if you want to do case case-insensitive search | ||||||||||||||||||||||||||||||||||||||||||||||||
| SourcePathAccessMode | Path access mode for file/folder
|
||||||||||||||||||||||||||||||||||||||||||||||||
| SourcePathValue | Source path or pattern | ||||||||||||||||||||||||||||||||||||||||||||||||
| SourcePathConnection | Connection name which holds source path | ||||||||||||||||||||||||||||||||||||||||||||||||
| SourcePathVariable | Variable name which holds source path/pattern | ||||||||||||||||||||||||||||||||||||||||||||||||
| Recursive | Specifies how to handle scanning of items | ||||||||||||||||||||||||||||||||||||||||||||||||
| ResultVariable | Variable which holds result of Get Property action or Get List As ADO.net table action. Variable has to be object datatype if you are storing list returned from Get action. | ||||||||||||||||||||||||||||||||||||||||||||||||
| TargetPathAccessMode | Path access mode for file/folder
|
||||||||||||||||||||||||||||||||||||||||||||||||
| TargetPathValue | Specifies target path | ||||||||||||||||||||||||||||||||||||||||||||||||
| TargetPathConnection | Specifies target path | ||||||||||||||||||||||||||||||||||||||||||||||||
| TargetPathVariable | Specifies target path | ||||||||||||||||||||||||||||||||||||||||||||||||
| OverWriteOption | Specifies how to handle overwrite action
|
||||||||||||||||||||||||||||||||||||||||||||||||
| CreateMissingTargetFolder | Specify this option if you want to create target folder automatically if its missing | ||||||||||||||||||||||||||||||||||||||||||||||||
| ContinueOnError | Continue on error if source file/folder is missing | ||||||||||||||||||||||||||||||||||||||||||||||||
| ExcludeRegXPattern | Regular expression pattern to exclude items from selection (if you apply MyFile*.* filter for source path and then use ExcludeRegXPattern=(\.msi$|\.exe$) then it will include all files with matching name but exclude *.msi and *.exe | ||||||||||||||||||||||||||||||||||||||||||||||||
| IncludeRegXPattern | Regular expression pattern to include items from selection (if you apply MyFile*.* filter for source path and then use IncludeRegXPattern=(\.txt$|\.csv$) then it will include only txt and csv files with matching name pattern | ||||||||||||||||||||||||||||||||||||||||||||||||
| TreatRegXForFullPath | Treat include/exclude Regular Expression as Full Path expression rather than file name. If you have a use case of checking against Folder name pattern along with file name then use this option. When you check this option then using ^ in the front of expression will not work for File name pattern so instead of something like this ^MyFile\w+\.csv$ you need to change prefix check like this [\\|/]MyFile\w+\.csv$ this way it works against full path check. | ||||||||||||||||||||||||||||||||||||||||||||||||
| SortBy | Property by which you want to sort result
|
||||||||||||||||||||||||||||||||||||||||||||||||
| SortDirection | Sort order (e.g. Ascending or Descending)
|
||||||||||||||||||||||||||||||||||||||||||||||||
| WhereClause | Where clause expression to filter items. (e.g. Size>100 and Extension IN ('.txt','.csv') ) | ||||||||||||||||||||||||||||||||||||||||||||||||
| EnableSort | Sort items by specified attribute | ||||||||||||||||||||||||||||||||||||||||||||||||
| MaxItems | Maximum items to return (e.g. TOP) | ||||||||||||||||||||||||||||||||||||||||||||||||
| SkipItems | Maximum items to skip (e.g. SKIP) | ||||||||||||||||||||||||||||||||||||||||||||||||
| LoggingMode |
|
||||||||||||||||||||||||||||||||||||||||||||||||
| PrefixTimestamp | When you enable this property it will prefix timestamp before Log messages. |
|
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 SSISIntroduction 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 SSISIntroduction 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 RegexIntroduction 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 LoopingIntroduction 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 CodingIntroduction 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 […] |