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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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.
"name": "Product 1",
"name": "Product 2",
"name": "Product 3",
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
- First drag data flow from SSIS Toolbox and then double click data flow
- Once Data flow designer is open, drag ZS JSON Source from SSIS Toolbox.
- 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.
- Go to Pivot Columns to Rows Tab and check Enable Pivoting Option
- 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
- For very Large file you need to Un-check Include Parent Columns option else you will get OutOfMemory Exception
- Now click Preview to see our data.
- Now you can connect your JSON / XML source to destination. See below example.
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
SELECT * FROM $
See below screenshot of JSON Driver Configuration to get idea.
Note: Use above query rather than what is displayed in the Screenshot.
BI / Reporting Tools Integration
|ETL Tools Integration||Programming Languages Integration|
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.