Pivot JSON and XML data using SSIS or ODBC Drivers

Introduction

In our previous post we saw various ways to transform JSON arrays. However there will be a time when your JSON / XML file wont have Array and you need to Pivot JSON Data.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Sample JSON data file

Here is a sample JSON file which we like to parse into rows and columns. Notice how it is using values inside in property name. This pattern needs Pivot option usage described later in this article.

If you like to play with real data then use below URL. This JSON has 60MB worth of data for all Products offered on AWS.  And It has similar structure as above except many more attributes under products node.

 

Using SSIS PowerPack to Pivot JSON data

ZappySys offers powerful JSON and XML Connectors. Below section will describe how to Parse and Pivot JSON data or Pivot XML data. For demo purpose we will use JSON Source but steps are almost same for XML too.

Step-By-Step JSON Pivot

  1. First drag data flow from SSIS Toolbox and then double click data flow
    Drag SSIS Data Flow Task from Toolbox

    Drag SSIS Data Flow Task from Toolbox

  2. Once Data flow designer is open, drag ZS JSON Source from SSIS Toolbox.
    Drag ZappySys JSON Source

    Drag ZappySys JSON Source

  3. Now for demo we will use Hard coded JSON using Direct Value option as below. You can also enter URL or File path if you select Direct Path option.
  4. Go to Pivot Columns to Rows Tab and check Enable Pivoting Option
    Pivot JSON / XML Data option for ZappySys API Connectors / Drivers

    Pivot JSON / XML Data option for ZappySys API Connectors / Drivers

  5. Now come back to Filter Options Tab here you can either browse Filter or enter by hand. If file is too large then enter by hand. For example in our case we will enter $.products  because we like to to parse structure below that node. If you have nested hierarchy (i.e. products node under orders node and so on) then use dot to separate them e.g.  $.customer.orders.products
  6. For very Large file you need to Un-check Include Parent Columns option else you will get OutOfMemory Exception
  7. Now click Preview to see our data.
    Pivot JSON Data using SSIS JSON Source

    Pivot JSON Data using SSIS JSON Source

     

  8. Now you can connect your JSON / XML source to destination. See below example.
    SSIS Package Execution - Loading Google BigQuery Data into SQL Server

    SSIS Package Execution –
    Loading Google BigQuery Data into SQL Server

Using ODBC PowerPack to Pivot JSON data

So far we talked how to use SSIS to read your JSON data and Pivot it but what if you want to consume JSON / XML / REST API data in some other App without doing ETL via SSIS?  Thats where ZappySys ODBC PowerPack comes in picture. Its a collection of many drivers including JSON Driver and XML Driver.  You can also use Data Gateway Approach, which allows T-SQL code to fetch JSON / XML data directly into SQL Server Table without any ETL / Programming.

Here is sample query you can run using JSON Driver or XML Driver

See below screenshot of JSON Driver Configuration to get idea.
Note: Use above query rather than what is displayed in the Screenshot.

Using ODBC DSN in C# code to call REST API

Using ODBC DSN in C# code to call REST API

 

Preview / Generate Query in JSON Driver / XML Driver

Preview / Generate Query in JSON Driver / XML Driver

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.   ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools Integration

ETL Tools Integration Programming Languages Integration
 

Conclusion

In this particle we saw how to use some advanced options for XML/ JSON Source and ODBC Drivers. You can download respective product depending your usecase. For SSIS usecase Download SSIS PowerPack and for other ETL / Reporting app integration scenarios (i.e. Power BI, Informatica, SSRS , Excel, MS Access, SQL Server, JAVA, C# ….) Download ODBC PowerPack.

 

Posted in JSON File / REST API Driver, SSIS JSON Source (File/REST), SSIS XML Source (File / SOAP), XML File / SOAP API Driver.