How to search and replace text in JSON file with SSIS

SSIS PowerPack JSON
,
1

Introduction

When working with JSON files in SQL Server Integration Services (SSIS), you may encounter situations where you need to search and replace specific strings. Whether you’re modifying configuration values, renaming keys, or updating data, automating this process can save time and reduce errors. In this guide, we will demonstrate how to efficiently search and replace strings in a JSON file using the ZappySys SSIS JSON source and Advanced File System Task.

Prerequisites

Steps

  1. Drag and drop a Data Flow Task onto the Control Flow.

  2. Double-click the Data Flow Task to enter the Data Flow workspace.

  3. Drag and drop the ZappySys JSON Source component onto the Data Flow.

  4. Open the JSON Source Editor and configure it:

    • Select a URL or file path containing the JSON data.
    • Apply a filter to extract the specific JSON object or node you want to modify.

  5. We will use Example 1 to demonstrate the search and replace function.

    image
    image828×745 40.8 KB

  6. Navigate to the Custom Replace tab and check the option Enable Custom Text Replace.

  7. Enter the text you want to search for and specify the replacement text.

  8. Click Preview Data to verify the changes.

    image
    image828×745 45.1 KB

Use Regular Expressions (Regex) for Advanced Search

  1. If you need to search for patterns, enable Regex Search; for more information, you can check our article here.
  2. In this example, we will use a regular expression to find a date in the format yyyy-mm-dd.
  3. Enter the following regex pattern:
    \b\d{4}-\d{2}-\d{2}\b--regex
  4. This pattern identifies and replaces any date in the specified format.
    image
    image828×745 38.5 KB

Using the Advanced File system task

  1. Another option is using the Advanced File system task with the action Search and Replace; it will work the same way, but you can select the search mode

    Advanced File system task configuration
    Advanced File system task configuration753×587 28 KB

  2. Select the option you want and enter the example you need

    Advanced File system task example
    Advanced File system task example763×590 30.6 KB

Conclusion

Using the ZappySys JSON Source component or the Advanced File system task, you can efficiently search and replace strings in a JSON file within SSIS. Whether making simple text replacements or using advanced regular expressions, this method ensures data consistency and saves time when working with large datasets.

For more advanced SSIS operations, explore the ZappySys SSIS PowerPack.

References