{"id":7975,"date":"2019-09-11T21:36:09","date_gmt":"2019-09-11T21:36:09","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7975"},"modified":"2023-09-11T20:52:55","modified_gmt":"2023-09-11T20:52:55","slug":"pivot-json-xml-data-using-ssis-odbc-drivers","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/pivot-json-xml-data-using-ssis-odbc-drivers\/","title":{"rendered":"Pivot JSON and XML data using SSIS or ODBC Drivers"},"content":{"rendered":"

Introduction<\/h2>\n

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

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Sample JSON data file<\/h2>\n

    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.<\/p>\n

    {\r\n\t\"version\": 1.0,\r\n\t\"products\": {\r\n\t\t\"P001\": {\r\n\t\t\t\"name\": \"Product 1\",\r\n\t\t\t\"price\": 10\r\n\t\t},\r\n\t\t\"P002\": {\r\n\t\t\t\"name\": \"Product 2\",\r\n\t\t\t\"price\": 11\r\n\t\t},\r\n\t\t\"P003\": {\r\n\t\t\t\"name\": \"Product 3\",\r\n\t\t\t\"price\": 12\r\n\t\t}\r\n\t}\r\n}<\/pre>\n
    If you like to play with real data then use below URL. This JSON has 60MB<\/strong> worth of data for all Products offered on AWS.\u00a0 And It has similar structure as above except many more attributes under products <\/strong>node.<\/p>\n
    https:\/\/pricing.us-east-1.amazonaws.com\/offers\/v1.0\/aws\/AmazonEC2\/current\/us-east-1\/index.json<\/pre>\n
     <\/p>\n
    Using SSIS PowerPack to Pivot JSON data<\/h2>\n
    ZappySys offers powerful JSON<\/a> and XML<\/a> 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.<\/p>\n
    Step-By-Step JSON Pivot<\/h3>\n
      \n
    1. First drag data flow from SSIS Toolbox and then double click data flow\n
      \"Drag
      Drag SSIS Data Flow Task from Toolbox<\/p><\/div><\/li>\n
    2. Once Data flow designer is open, drag ZS JSON Source from SSIS Toolbox.\n
      \"Drag
      Drag ZappySys JSON Source<\/p><\/div><\/li>\n
    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.<\/li>\n
    4. Go to Pivot Columns to Rows<\/strong> Tab and check Enable Pivoting<\/strong> Option\n
    5. Now come back to Filter Options<\/strong> Tab here you can either browse Filter or enter by hand. If file is too large then enter by hand<\/strong>. For example in our case we will enter $.products<\/span>\u00a0 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.\u00a0$.customer.orders.products<\/span><\/li>\n
    6. For very Large file<\/strong> you need to Un-check Include Parent Columns<\/strong> option else you will get OutOfMemory Exception<\/li>\n
    7. Now click Preview to see our data.\n
    8. Now you can connect your JSON \/ XML source to destination. See below example.\n
      \"SSIS<\/a>
      SSIS Package Execution –
      Loading Google BigQuery Data into SQL Server<\/p><\/div><\/li>\n<\/ol>\n
      Using ODBC PowerPack to Pivot JSON data<\/h2>\n
      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?\u00a0 Thats where ZappySys ODBC PowerPack<\/a> comes in picture. Its a collection of many drivers including JSON Driver<\/a> and XML Driver<\/a>.\u00a0 You can also use Data Gateway Approach<\/a>, which allows T-SQL code to fetch JSON \/ XML data directly into SQL Server Table without any ETL \/ Programming.<\/p>\n
      Here is sample query you can run using JSON Driver or XML Driver<\/p>\n
      SELECT * FROM $\r\nWITH(\r\n\t Src='https:\/\/pricing.us-east-1.amazonaws.com\/offers\/v1.0\/aws\/AmazonEC2\/current\/us-east-1\/index.json'\r\n\t,Filter='$.products'\r\n\t,IncludeParentColumns='False'\r\n\t,EnablePivot='True'\r\n)<\/pre>\n
      See below screenshot of JSON Driver Configuration to get idea.
      \nNote:<\/strong> Use above query rather than what is displayed in the Screenshot.<\/p>\n
      \"Using<\/a>
      Using ODBC DSN in C# code to call REST API<\/p><\/div>\n
       <\/p>\n
      \"Preview<\/a>
      Preview \/ Generate Query in JSON Driver \/ XML Driver<\/p><\/div>\n
      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.\r\n\r\n \r\n\r\n\"ZappySys\r\n\r\n\r\n\r\n\r\n
      \r\n
      BI \/ Reporting Tools\r\nIntegration<\/strong><\/p>\r\n<\/td>\r\n
      		ETL Tools\r\nIntegration\r\n<\/strong><\/td>\r\nProgramming Languages<\/strong>\r\nIntegration<\/strong><\/td>\r\n<\/tr>\r\n
      \r\n\r\n\r\n