Consuming JSON data in SQL Server and SSIS, convert JSON to XML

Just came across very interesting scenario where I had to consume JSON data in SQL Server. Yes I can write C#/VB.net code and load JSON to SQL Server and go from there but what if I have no expertise in C#/VB.net or what I have to do everything using SQL no other options are allowed. Well check below link and you will find some kool techniques for consuming and generating JSON in SQL Server.

Import JSON data in SQL Server

Importing JSON into SQL Server requires you to Deformalize JSON structure and import selected columns. In this section we will learn how to use SSIS JSON Source to Import JSON File or HTTP URL Stream into SQL Server Table.

1. Download and Install SSIS JSON Source Connector
2. From your tool box Drag SSIS JSON Source
3. Specify JSON File or URL from where you want to consume JSON data
4. Click on Columns tab and select columns you want to select
5. Drag OLEDB Destination. Select SQL Server connection and Target Table where you want to load data
6. Map Input columns to Target SQL Server Table
7. Run the package

Export JSON data in SQL Server

Now lets look at how to export SQL Server data to JSON file. You can do this two different ways. Use Command Line to create JSON file or use SSIS JSON File Export Task to export to JSON

1. Download and Install SSIS Export JSON File Task
2. In Control Flow From SSIS tool box Drag SSIS Export JSON File Task
3. Specify Connection from where you want to read data
4. Specify tables or query for source. You can pick multiple tables
5. If you want custom layout of your JSON then enable Custom layout option on layout Tab (e.g. Nested nodes or array of value)
6. Specify Target path (folder or file name)
7. Run the package

Hope this is helpful

Cheers!!!

Consume XML or JSON in SQL SERVER – SSIS