How to use Custom Replace feature in JSON / XML / CSV Source or ODBC Drivers

nayan · December 6, 2023, 3:30pm

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.

Custom SSIS Components - JSON Source (File and REST API Connector) JSON Source Connector (File, REST API)

Custom SSIS Tasks - XML Source Connector (File, SOAP, REST API) XML Source Connector (File, SOAP, REST API)

CSV Source (GET, POST, DELETE etc) 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.