Introduction
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:
Prerequisites
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- ZappySys SSIS PowerPack installed.
Step-by-Step – Export JSON to CSV File with SSIS
- From SSIS Toolbox drag and drop Data Flow into the Control Flow.
- Then drag and drop JSON Source (REST API or File) into the Data Flow.
- Open JSON Source component for modifications.
- Load JSON directly, from variable or URL:
- Use JSONPath Expression in Filter section to select the data you need. We will use $.store.book[*] to extract all books from JSON array. Check Understanding JSONPath Expression with Examples article to see more examples how this filter works.
- Close the window.
- Drag and drop Flat File Destination into Data Flow and connect it to JSON Source:
- Open Flat File Destination for configuration, configure it and map JSON Source columns with Flat File Destination columns.
- 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:
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:
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: JSON array appears as one column
This would require to make a Simple Transformation on source JSON:
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
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)".
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.
Conclusion
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.
