Using SSIS to Export Data in JSON Format

By: Parvathy Natraj | Updated: 2018-04-03 | Comments (3) | Related: More > Integration Services Development

Problem

How do I export data in JSON format using SQL Server Integration Services?

Solution

SQL Server Integration Services (SSIS) has been used widely as an ETL tool. In addition, you can also use SSIS to export data to files such as CSV, tab delimited, Excel or XML. In this tip, we will export data in JSON format using SQL Server Integration Services.

Sample Database

It is assumed that you have downloaded and installed the latest version of the sample database "AdventureWorks" from GitHub here. In this tip, I will be using the SalesOrderHeader and SalesOrderDetail tables from AdventureWorks sample database.

We will be analyzing two methods to export data in JSON format. The first method is relatively simple and the second method is little complex but has many advantages. Let’s have a quick look at the first method.

Sample SQL Query to generate data in JSON format

The below T-SQL query will generate data in JSON format.

SELECT TOP 100 SalesOrderID, OrderDate, SalesOrderNumber, CustomerID 
FROM [Sales].[SalesOrderHeader]
FOR JSON AUTO

Export data in JSON format: Method 1

In this method, we will be creating a SSIS package with a dataflow. In this data flow, we will extract data using the above-mentioned T-SQL query. The resultant JSON data content will be sent to a flat file destination.

In this way, we can easily export data in JSON format based on the T-SQL query.

The below image shows a SSIS package with a data flow task.

This SSIS package has two connection managers. An OLEDB connection manager helps extract data from the AdventureWorks database and a flat file connection manager will allow the SSIS to redirect the JSON data content to a flat file.

The below image represents the flat file connection manager configuration details. Note that "Unicode" has been enabled as the T-SQL code will return JSON output as Unicode characters.

A column "JSON" has been added to the flat file connection manager with the datatype "Unicode text stream[DT_NTEXT]".

The below image provides the data flow details at a very high level. The OLEDB extracts data from the database and returns a JSON dataset. The resultant dataset will be sent to the flat file connection manager. This way, we can export the relational data in JSON format.

The SQL query has been configured to extract data from the Adventureworks database as per the image below in the OLEDB source.

It is observed that the resultant dataset will return the JSON dataset with the auto generated column name. Hence the output column name has been renamed as "JSONOutput".

The below image represents the flat file destination adapter configuration and the mapping details.

ssis Flat File Destination Adapter Properties

The below image confirms that the SSIS package has been successfully executed and a JSON output file has been created in the destination folder.

Here is what the exported data looks like.

Export data in JSON format: Method 2

This method will make use of the script task to export data in JSON format. In this method, a script task will execute the SQL query. The output of the T-SQL query will be read using C# and the result set will be written to a flat file.

Source Code Details

In the C# code, a StreamWriter has been used to open the JSON output file. The content of the JSON result set from the T-SQL query will be written to this file. The T-SQL query has been assigned to a string variable.

The ADO.NET connection can be invoked using Dts.connection and it will return a connection manager object. The connection can be acquired using the AcquireConnection method in the connection manager object.

A SQLCommand object can be created by passing the SQLConnection and the T-SQL query to the SQLCommand constructor. The T-SQL command object can be executed using the method ExecuteReader. The ExecuteReader method will return a SQLDatareader object. The SQLDataReader object will contain the resultant dataset. The actual value can be read using the Read method. The actual data can be assessed using the column index. The first column value can be assessed using index 0. The result set will be transformed as JSON output and there will be only one column. Once the data value has been read, it can be written to the JSON output file.

public void Main()
{
   // TODO: Add your code here
 
   ConnectionManager cm;
   string sqlString = "";
 
   System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\SSIS_JSON_GEN\JSONOutput.txt");
 
   sqlString = "Select top 100 SalesOrderID ,OrderDate ,SalesOrderNumber ,CustomerID from [Sales].[SalesOrderHeader] for JSON AUTO";
   System.Data.SqlClient.SqlConnection sqlConn;
   System.Data.SqlClient.SqlCommand sqlComm;
 
   cm = Dts.Connections["Localhost_Staging"];

   sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
   sqlComm = new System.Data.SqlClient.SqlCommand(sqlString, sqlConn);
   System.Data.SqlClient.SqlDataReader reader = sqlComm.ExecuteReader();
   try
   {
       while (reader.Read())
       {
          file.WriteLine(reader[0]);
       }
   }
   finally
   {
        // Always call Close when done reading.
        reader.Close();
   }
 
   cm.ReleaseConnection(sqlConn);
   Dts.TaskResult = (int)ScriptResults.Success;
}

After successful execution of the package, the JSON output file has been created.

Building a Scalable Solution

In the above examples, we hardcoded the location of the file and the SQL query in the script task. It is always recommended to maintain the details (such as the file name and the T-SQL query) in a variable. So that any changes to these parameters can be done without making changes to the SSIS package. In addition, I have also created a simple stored procedure to convert the relational data into JSON data format.

The below T-SQL script will create a stored procedure to transform data from the SalesOrderHeader and SalesOrderDetails tables to JSON output.

CREATE PROC GetSalesDetails
AS
SELECT SH.SalesOrderID 'SalesHeader.SalesOrderID', 
   SH.OrderDate 'SalesHeader.OrderDate',
   SH.SalesOrderNumber 'SalesHeader.SalesOrderNumber',
   SH.CustomerID 'SalesHeader.CustomerID',
   JSON_QUERY(( 
      SELECT 
      SalesOrderDetailID 'OrderDetail.SalesOrderDetailID',SD.ProductID 'OrderDetail.ProductID',SD.LineTotal  
      'OrderDetail.LineTotal' 
      FROM [Sales].[SalesOrderDetail] SD
      WHERE SH.SalesOrderID = SD.SalesOrderID
      For JSON PATH , Without_Array_Wrapper 
      )) SalesOrderDetails
FROM [Sales].[SalesOrderHeader] SH 
FOR JSON PATH

The below image shows that two new variables have been created in the SSIS package. The variable "strOutputFileName" will have the value of the JSON output file and the variable "strSQLStatement" will contain the actual T-SQL script.

Both variables have to be referenced in the script task as shown in the image below.

ssis Variables Configuration for Script Task

The C# code has been extended to read the values of the variables using the Dts.Variables object.

 public void Main()
{
   // TODO: Add your code here
 
   ConnectionManager cm;
   string sqlString = Dts.Variables["User::strSQLStatement"].Value.ToString();
   string OutputFileName = Dts.Variables["User::strOutputFileName"].Value.ToString();

   System.IO.StreamWriter file = new System.IO.StreamWriter(OutputFileName);
 
   System.Data.SqlClient.SqlConnection sqlConn;
   //System.Data.OleDb.OleDbConnection sqlConn;
   //System.Data.OleDb.OleDbCommand sqlComm;
   System.Data.SqlClient.SqlCommand sqlComm;
 
   cm = Dts.Connections["Localhost_Staging"];
 
   sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
   sqlComm = new System.Data.SqlClient.SqlCommand(sqlString, sqlConn);
   //sqlComm.ExecuteNonQuery();
   //sqlConn.Open();
   System.Data.SqlClient.SqlDataReader reader = sqlComm.ExecuteReader();
   try
   {
      while (reader.Read())
      {
         //Console.WriteLine(String.Format("{0}, {1}",reader[0], reader[1]));
         file.WriteLine(reader[0]);
      }
   }
   finally
   {
      // Always call Close when done reading.
      reader.Close();
   }
 
   cm.ReleaseConnection(sqlConn);
   Dts.TaskResult = (int)ScriptResults.Success;
}

After the successful execution of the package, a JSON output file has been created.

Summary

In this tip, we learned two methods to export data in JSON format using SSIS. It is assumed that the source server is on the latest edition of SQL Server (at least SQL Server 2016) to natively support the AUTO and PATH JSON options.

Next Steps

Learn JSON basics with this tip
Challenge your JSON knowledge with this tip
Read more about JSON_VALUE here

About the author

Parvathy Natraj is an independent Agile Data Analyst in the UK with Master Degree in Business Administration. She is an ISEB Certified tester and Microsoft Certified System Administrator.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-04-03

Using SQL Server Integration Services to Export Data in JSON Format