- 1 Introduction
- 2 Prerequisites
- 3 Step-by-Step – Export JSON to CSV File with SSIS
- 4 Using API as JSON source
- 5 Exporting multiple JSON arrays to a CSV
- 6 Transforming JSON array
- 7 Common Errors
- 8 Deployment to Production
- 9 Download a sample package
- 10 Conclusion
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:
- 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:
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.