Following SSIS Components and ODBC Drivers come with a very powerful feature which can be used in some interesting scenarios. For example, if you have JSON / XML or CSV with certain characters and you like to replace them with blank values or some other characters then you can use this feature.
JSON Source Connector (File, REST API) XML Source Connector (File, SOAP, REST API) CSV File Source
How to use Regular Expression for Custom Search / Replace in SSIS
By default search / replace is a plain text search. If you like to invoke Regular Expression then use the suffix --regex or --regex-ic (case-insensitive search) for search pattern
You can use $1, $2… $N etc for extracting Groups (where N=Group number). $1 is the Full match, $2 is the match from 2nd group (e.g. (.+) in the following example to extract column name)
Select Custom Replace Tab
Click here to learn more about Regular Expressions
How to perform regex custom replace in ODBC Drivers
The same feature you saw in SSIS Components also available in the following ODBC Drivers
The following 12 drivers support the custom replace feature
JSON / XML / CSV Drivers
Amazon S3 JSON / XML / CSV Drivers
Azure Blob JSON / XML / CSV Drivers
SFTP JSON / XML / CSV Drivers
Here is where to find this feature.
Here are a few links.
JSON File / REST API Driver XML File / SOAP API Driver CSV File / REST API Driver Amazon S3 Driver for JSON Files Amazon S3 Driver for XML Files Amazon S3 Driver for CSV Files Azure Blob Driver for JSON Files Azure Blob Driver for CSV Files FTP / SFTP Driver for JSON Filess
