How to encode and decode JSON values in SSIS

SSIS PowerPack JSON
1

Introduction

Working with JSON data is common in modern data integration scenarios, especially with web services or APIs. However, JSON strings often contain special characters like newlines, tabs, or escape sequences, making data processing more challenging. In SSIS (SQL Server Integration Services), handling JSON data effectively requires encoding and decoding these unique characters to ensure proper data flow and usability.

This article will guide you through decoding and encoding JSON values using the ZappySys SSIS PowerPack. We will demonstrate how to handle common JSON challenges such as replacing newline (\r\n), tab (\t), and other special characters, ensuring your data is processed cleanly and efficiently.

By the end of this article, you will have a clear understanding of how to utilize the FUN_JSONDEC and FUN_JSONENC functions, enabling you to transform JSON strings into readable formats and convert them back for further usage or storage.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from this site.

Steps

How to Decode JSON

In this section, we will use the function FUN_JSONDEC, which decodes a JSON string by replacing escape sequences (e.g., \r\n becomes {NEWLINE}, \" becomes a double quote, and \t becomes {TAB}).

  1. Drag and drop a Data Flow into your package.

  2. Inside the Data Flow, drag a JSON source, enter the URL or file with JSON data, specify the object filter, and preview the data. For example, use this JSON:

    [
    {
        "name": "AAA\r\nBBB\r\nCCC",  // \r\n is replaced by {NEWLINE}
        "date": "2015-01-03"
    },
    {
        "name": "Col1\tCol2",  // \t is replaced by {TAB}
        "date": "2015-01-03"
    }
]

    JSON Source Example
    JSON Source Example828×745 39.8 KB

  3. Connect a Template Transform Component to the JSON source. We will use the FUN_JSONDEC function like so:
    "name": "<<<%name%>,FUN_JSONDEC>>"

    Decode JSON column
    Decode JSON column760×569 40.1 KB

  4. Connect a destination component and run the package.
    Decode package

  5. Here’s the decoded result saved in a file:
    Decode Example

How to Encode JSON

Next, we’ll use the FUN_JSONENC function to encode special characters (e.g., {NEWLINE} becomes \r\n, {TAB} becomes \t).

  1. Drag and drop a Data Flow into your package.

  2. Inside the Data Flow, drag a JSON source, enter the URL or file with JSON data, specify the object filter, and preview the data. Use this JSON for the example:

    [
    {
        "name": "Col1\tCol2",  // {TAB} becomes \t
    },
    {
        "name": "AAA\nBBB\nCCC",  // {NEWLINE} becomes \n
    }
]

    JSON Source Example
    JSON Source Example828×745 39.8 KB

  3. Connect a Template Transform Component to the JSON source. Apply the FUN_JSONENC function:
    "name": "<<<%name%>,FUN_JSONENC>>"

    Encode JSON column
    Encode JSON column838×552 40.7 KB

  4. Connect a destination component and run the package.
    Encode package

  5. Here’s the encoded result saved in a file:
    Encode Example

Video Tutorial

Conclusion

By following the steps outlined in this article, you can efficiently decode and encode JSON values using the ZappySys SSIS PowerPack. The FUN_JSONDEC function simplifies the decoding of JSON strings by replacing escape sequences with readable characters like newlines or tabs, making the data more user-friendly. Similarly, the FUN_JSONENC function allows you to re-encode those characters into their respective JSON formats, preserving the data structure and ensuring compatibility for further processing or storage. Should you face any issues or need further assistance, our support team is ready to help via chat on our website

References