5 Ways to Export JSON from SQL Server using SSIS

Introduction

SQL Server 2012 and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easily output complex JSON using any of the following components.

Five ways of creating JSON inside SSIS

ZappySys toolset provides multiple ways creating JSON inside SSIS. You can choose method suitable for your scenario. Each method has pros and cons so choose it as per your need.

JSON File Destination

SSIS JSON Destination can be used to generate simple or complex JSON files out of relational data source such as SQL Server, Oracle, MySQL. In few clicks you can create desired JSON Shape from single or multiple tables (Parent/Child Nested Documents).

PROS: Multiple data flow components can be attached. Which means unlike Export JSON Task you are not limited to OLEDB or ODBC Sources only.

CONS: When you remove upstream component it may break Dataset mappings. Also server side Lookup Option is not available. File Splitting Options not available.

Here are some articles for use case of this component.

JSON Generator Transform

SSIS JSON Generator Transform can be used to generate single or multiple JSON documents from any type of data sources (e.g. SQL Server, MySQL, Flat File, Excel) inside data flow task. It takes multiple inputs and outputs JSON String Output Column which can be used to feed JSON to other downstream system.

PROS: Multiple data flow components can be attached. Which means unlike Export JSON Task you are not limited to OLEDB or ODBC Sources only.

CONS: When you remove upstream component it may break Dataset mappings. Also server side Lookup Option is not available. File Splitting Options not available. Output is JSON String Column.

Here are some articles for use case of this transform.

Export JSON Task

SSIS Export JSON File Task can be used to generate simple or complex JSON files out of relational data source such as SQL Server, Oracle, MySQL. It also support exporting directly to cloud (e.g. Azure Blob Storage, Amazon AWS S3, Secure FTP). In few clicks you can create desired JSON Shape from single or multiple tables (Parent/Child Nested Documents)

PROS: Simple to use with flexible options to export to file, variable, AWS S3, Azure Blob, FTP / SFTP location. Allows to split records. File Splitting Options available (Split By Size  / Row Count).

CONS: You can only use limited data sources as Dataset input (e.g. OLEDB / ODBC or SQL Server).

Here are some articles for use case of this task.

Template Transform

Template Transform can be used to create dynamic strings inside data flow. You can use upstream columns as placeholders or use SSIS variables anywhere in the template to create desired text (e.g. XML / JSON document).

PROS: Very Simple to use.

CONS: Cannot use multiple inputs. It may not be used to create Complex JSON and options like do not output NULL attributes is not possible.

 

Here are some articles for use case of this transform.

Logging Task (Control Flow Approach)

 Just like Template Transform you can use ZS Logging Task to create JSON string from Template and Variable Placeholders.

 

 

 

Here is how to use Logging Task to save JSON into a variable.

1. Select Variable which you like to Set on Logging Task
2. Enter your Template JSON String which you like to produce (see example below). Notice the use of yyyy-MM-dd format specifier and also JSONENCODE function.
   
   JavaScript

   { ActionId: {{User::vActionId}}, CreatedOn: "{{User::vOrderDate,yyyy-MM-ddTHH:mm:ss}}", Notes: "{{User::vNotes,JSONENCODE}}" }

   1 2 3 4 5

   { ActionId: {{User::vActionId}}, CreatedOn: "{{User::vOrderDate,yyyy-MM-ddTHH:mm:ss}}", Notes: "{{User::vNotes,JSONENCODE}}" }

3. Select Message Type = None
4. Uncheck Append, and Prefix options like below
   

   Creating JSON / XML Document using SSIS Logging Task

 

Here are some articles for use case of this task.

 

Create JSON using SSIS Export JSON Task

Now let’s look at Export JSON Task form example to create JSON. Steps listed in below sections are very similar to JSON File Destination or JSON Generator Transform.

ZappySys offers innovative Layout Editor for JSON / XML creation from multiple sources. You can use Layout Editor to produce desired JSON Layout (Layout is stored in XML format behind the scene) and then in later step you will see how to make it dynamic so values are changed at runtime.

You can read layout as direct value, read from variable or read from file from disk.

Select source connection for data

First lets create new OLEDB or ADO.net connection from where we will read data. In our case we reading from Northwind Database.

Create Datasets

Now add datasets which will be used to produce JSON File. One dataset will be always Root Dataset (Example: If your Root information is about customer then select customer table or query as your Main dataset. All other sections will be child sections)

Add Output Columns for Main JSON Document

Now its time to add columns for our output. If * is added by default then remove. * means output all columns. When you add columns first make sure you select correct parent node under which you want to add columns. In our case “Mappings” is parent node.

Add JSON Array / JOIN Conditions for related document

Now let’s look at how to add JSON Array. For example Customer may have one or more Orders so we need to add node called Orders which may Produce JSON fragment like below

Orders : [ {...} , {...} ] 

Once customer related columns added, its time to add Orders section.

1. This is Array of documents so first highlight “Mappings” node
2. Click “Add Document Array” option from toolbar.
3. On Add/Edit Elements UI specify name for JSON Attribute and specify correct relationship to parent dataset. In our case Orders related to Customers using CustomerID key.

Handling JSON Array with dummy root dataset (Multiple Arrays)

So in previous section we saw how to add array and define JOIN condition with parent dataset. Now what if you really have no parent dataset to join with ? For example we need to produce following sample JSON where Order array is related to Customer by common Column CustomerID as JOIN column. But Customer is not bound with any parent column. So how do you define JOIN condition? In such case you have to use below technique called dummy root dataset. (See next section on how to handle only one dataset using Single Dataset Array Mode)

Sample JSON (Dummy root pattern)

Since root level always needs dataset, we must provide dummy dataset (only one row) even though there is no need for Parent Dataset JOIN in Customer Array. Typically you can write query like below to product dummy row in SQL Server or other database using OLEDB Source or Use Dummy Data Source with Max Rows=1 setting to produce one fake record.

Using Dummy DataSource as Input

For generating JSON which needs dummy root. Perform the following steps

1. Drag Dummy Data Source from SSIS Toolbox. Change How many rows to 1. Click OK
2. Now drag two more OLEDB sources and Configure (e.g. Customers and Orders)
3. Now drag ZS JSON FIle Destination component or ZS JSON Generator
4. Now connect Dummy datasource then Customers and then Orders
5. Double click JSON component to configure layout. You may notice that Dummy Input is set as Root by default.
6. You can now select Mappings node and click on Add Array Documents icon. Configure like below. Notice we have not selected any JOIN criteria because its joined with Dummy Root.
   

   Add array to join with dummy dataset

7. Now select newly added node and Click Add column icon to add CustomerID, CompanyName column under Customers array
8. Now select Customers array node and click Array Documents icon to Add Orders array.
9. Once Orders Array ia added you can Select Orders node and Insert new element to add OrderID and OrderDate columns
10. Finally it will look like below
    

    JSON File Destination – Generate JSON in SSIS from multiple inputs

 

Single dataset used as array

Now lets look at one more pattern just like described in the previous section but now we have just one nested level (Only customers and not using Orders). If you have such case then choose Single dataset pattern option in the output mode. On Export Task you may see as check box rather than dropdown. To produced desired JSON (see blow) perform these steps.

1. Select Output mode as Single Dataset Array
2. In your Layout builder click on Add Document Array Icon and name it “Customers”.
3. After that highlight newly added node > Right-Click > Add Elements under that node
4. That’s it you will now see preview like below.

 

How to export JSON in 2D Array format

If you have to export JSON as 2D array format then you can change following option.

Here are examples of output for each option.
Select say you have source data with 2 columns id and name.

Default

Multicontent

ArrayDocs

Array2D

Array2DWithHeader

ArrayLines

ArrayLinesWithHeader

 

Preview JSON output using live preview

You can enable / disable Live preview of JSON layout as below.

Make things dynamic (Using SSIS Variable for SQL or Layout)

There will be a time when you have use dynamic values in your JSON generation. You may have to Alias something from variable or you have to supply Variable in your SQL used for Dataset. Not to worry. ZappySys Supports multiple ways to make it dynamic. You can use SSIS Variables following 3 ways to make Layout XML dynamic.

1. Use SSIS variable inside Dataset SQL (Only for ZS Export JSON Task)
2. Anywhere inside Layout XML (Click Edit Code option)
3. Load Layout From Variable (Indirect Method)

Using Variable inside Dataset SQL

Here is how you can use Variable inside dataset SQL for ZS Export JSON Task

Using Variable inside Layout XML

Here is how you can insert SSIS Variable anywhere in Layout XML. In below Example we are choosing Alias for one column from some SSIS Variable.

Load Layout From Variable

If you have some need to load Layout XML from File or SSIS Variable. You can use Load Layout from Variable Option.

For example in below screenshot we are creating layout first and ave to some variable. Or you can load from external file using ZS Advanced File System Task.  Connect Logging task to Export JSON Task and you ready to produce JSON.

Here is how to load Layout From any SSIS Variable. You can also load Layout from XML file or Variable which contains layout File Path.

Other JSON Output Options

Execute package and verify generated JSON it should look like below (Date format may be different in your case if you have set different on JSON options tab). For clarity we have formatted JSON below but in your output it may be compact version so reduce file size.

Indent JSON Output

You can check Indent option to make JSON look pretty like below.

Hide NULL Attribute Option

There will be a time when you want to Hide NULL attribute rather than showing MyAttr: null this is also possible by checking Hide NULL Attribute Option on Options Tab.

Export JSON to Amazon S3, Azure Blob, Secure FTP (SFTP / FTPS)

Export JSON Task support some really good options to save file to other than local file system. If you want to export JSON directly to Amazon / Azure Cloud or FTP Server without saving to local disk then you can use Azure / FTP / Amazon Connection Manager on Target Tab of Export JSON Task. This option is only available for PRO Edition Users.

Conclusion

If you want to output JSON from SQL Server or any other relational DB then its not simple task in SSIS specially when you have nested JSON documents (e.g. Customer->Orders). In this post you have seen how to use SSIS JSON Export Task to generate nested JSON from multiple datasets.