How to convert JSON to CSV File using SSIS


In this article, you will learn how to convert JSON to CSV File using SSIS. Sometimes you need to convert JSON file to a CSV, which resides in your local disk or somewhere on the Internet, be it a simple download link or a more sophisticated API call. Using ZappySys SSIS PowerPack together with SSIS will make it easy to happen.

We will be using this ZappySys SSIS PowerPack component to make things work:


  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. ZappySys SSIS PowerPack installed.

Step-by-Step – Export JSON to CSV File with SSIS

  1. From SSIS Toolbox drag and drop Data Flow into the Control Flow.
  2. Then drag and drop JSON Source (REST API or File) into the Data Flow.
  3. Open JSON Source component for modifications.
  4. Load JSON directly, from variable or URL:
    Convert JSON to CSV File using SSIS
  5. Use JSONPath Expression in Filter section to select the data you need. We will use $[*] to extract all books from JSON array. Check Understanding JSONPath Expression with Examples article to see more examples how this filter works.
  6. Close the window.
  7. Drag and drop Flat File Destination into Data Flow and connect it to JSON Source:
  8. Open Flat File Destination for configuration, configure it and map JSON Source columns with Flat File Destination columns.
  9. You are done! JSON will be exported to a CSV (download a sample package).

Using API as JSON source

If your JSON happens to reside in a response of an API call which uses OAuth authentication, read this article how to configure JSON Source OAuth connection:

How to authenticate to an API with OAuth 2.0 using SSIS

Exporting multiple JSON arrays to a CSV

There may be a need to export two or more JSON arrays to several CSVs. In that case, you might be interested in this article:

Reading JSON Arrays from file / REST API

Transforming JSON array

Another use case you may encounter is when row values are placed in a JSON array. And then you desperately want to transform that array so that you could see those values in separate columns:

Convert JSON to CSV File using SSIS: JSON array appears as one column

Convert JSON to CSV File: JSON array appears as one column

This would require to make a Simple Transformation on source JSON:

Convert JSON to CSV File using SSIS: Transforming JSON array values into columns

Convert JSON to CSV File : Transforming JSON array values into columns

We also support transforming other formats of JSON arrays. Check how to transform them all in the article Parse multi-dimensional JSON array in SSIS.

Common Errors

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

Download a sample package

Be sure to download a sample SQL Server 2012 SSIS package in case you want to try it right away.


In this article we learned how to convert JSON to a CSV using SSIS PowerPack JSON Source in a very simple way. We also touched topics that delve into more complicated scenarios such as grabbing JSON from an API, exporting multiple JSON arrays and transforming them into columns. Check our Blog articles on JSON Source to find out what JSON Source is capable of more.

Posted in SSIS Components, SSIS JSON Source (File/REST), SSIS PowerPack and tagged , , , , .