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
Read Json File data from Web Url Example in SSIS
SSIS Json Source Adpater - Output, Read, Copy Json Data from Web Url or File
SSIS Json Source Adpater - Output, Read, Copy Json Data from Web Url or File
How to read-extract json records from file in SSIS

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
SSIS Generate JSON File Task - Generate JSON files for selected tables/views

SSIS Export JSON File Task – Generate JSON files for selected tables/views

SSIS Export JSON File Task - Select Target Path and other options

SSIS Export JSON File Task – Select Target Path and other options

SSIS Export JSON File Task - Layout editor for nested JSON elements

SSIS Export JSON File Task – Layout editor for nested JSON elements

SSIS Export JSON File Task - Split JSON Data into Multiple files

SSIS Export JSON File Task – Split JSON Data into Multiple files

SSIS Export JSON File Task - JSON Date Formatting, Encoding Options

SSIS Export JSON File Task – Date Formatting, Encoding Options

SSIS Export JSON File Task - Execution Log

SSIS Export JSON File Task – Execution Log

Hope this is helpful

Cheers!!!

Consume XML or JSON in SQL SERVER – SSIS

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Tagged with: , , , ,
Posted in SQL Server

Leave a Reply