SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exporting Data from JSON to CSV with SSIS

By Daniel Calbimonte,

Introduction

JSON (Java Script Object Notation) is becoming more popular. SQL Server, Tabular Databases, Web Services, DocumentDB also use JSON for different purposes. That is why we will talk about JSON and how to import JSON data to a CSV file using SQL Server Integration Services (SSIS). 

If you have no experience in JSON, this article was created for you. We created a nice step-by-step tutorial.

Requirements

Please be sure the following are completed before trying the code in this article.

Getting Started

In this example, we will import the data from a JSON file to a CSV file. The JSON file named bookshop.json can be downloaded from the Resources section files at the end of this article. Here is what is contained in the file:

{ "bookstore": {
    "book": [ 
      { 
        "title": "Don Quixote",
        "genre": "novel",
        "author": "Miguel de Cervantes",
        "InStock": 200,
        "language": "Spanish"
      },
      { 
        "title": "Cyrano de Bergerac",
        "genre": "romance",
        "author": "Edmond Rostand",
        "InStock": 126,
       "language": "French"
      },
      { 
        "title": "The Three Musketeers",
        "genre": "historial novel",
        "author": "Alexandre Dumas",
        "InStock": 356,
        "language": "French"
      }
    ]
  }
}   

The JSON file contains book information, including titles, genre, language and authors of the books. We will export the data to a CSV file. We will use a free component from Codeplex. It is the SSIS JSON Source Component. This is an external component used to extract information from JSON. You can download it hereThis is a free component of 550 KB approximately.

Once downloaded, execute it. The installer will install the components in SSIS:

Go to SSDT and then go to File>New Project

Go to Integration Services>Integration Services Project:

Drag and drop Data Flow Task to the design pane:

Double click Data Flow to enter to the Data Flow section and drag and drop JSON Source Component (this is the new component installed to handle JSON files):

Drag and drop the Flat File Destination component. This Task will be used to create the destination CSV file:

Return to the  Source JSON component. In Settings, select direct Input and press browse to select the bookshop.json file. You can specify a local file or a URL. Abouth the methods, use get. Get is used to read data. Here you have a table with the functionallity of each method:

Method Operation
GET Read data
POST Create
PUT Update/Replace
DELETE Delete

Once selected the get option and the file, press Test:

Test will display errors if there are problems. If everythig is green it means that everything is OK:

Go to Columns. In columns we have the following parameters:

Json response type

Go to JSON response type, and select JsonObject. Response type is used to specify the format of the data received. If the data received is an array, you can specify the Array type. In most of the cases, it is an object. If there are brackets at the beginning and at the end of the end of the file, it is an array.

Path to Json Array/Object from response

Path is used to specify the position of the objects in a JSON file. In this example, we will select author, gender and language fields from the bookshop.json file. In our JSON file, these fields are inside the book object and book is inside bookstore:

  bookstore>book>author

In JSON paths, the root (in this case bookstore), requires a $ to indicate that it is the root. The Path to Json would be:

$.bookstore.book

JSON Field Name 

Here you specify the name of the fields in JSON. In our JSON file we want the following fields:

  • author
  • genre
  • language

Max Length

Max lenght depends on Column type. In this example, the max size for string is 255 characters.

OutputColumnName

This field is the name of the columns exported. You can use the same name of the JSON Field Name or use another name.

Column type

It is used to specify if the value is a sring, a number, etc.

Once selected the values, specify the values and press test:

If everything is OK, you will be able to watch the book information of the first object like the title, genre of the first book in the JSON file:

The first object is Don Quixote in the bookshop.json file:

Go to Flat File Destination:

In Flat File Destination, press New:

Select delimited to generate a file with columns separated by columns:

Press browse to select the path of the flat file to create:

Specify a name for the CSV file. In this example, the file name will be mycsv and the type CSV:

Press OK to accept the configuration:

Press Mappings to Map the JSON column to the new CSV file:

Mappings will map all the columns from source to target:

Press Start to run the package:

If everything is OK, you will be able to see mycsv.csv with the data created:

Conclusion

In this article, we exported data from JSON to a CSV file. It is very easy with SSIS to export to SQL Server, Oracle or any other database of your preference.

References

 

Resources:

bookshop.json
Total article views: 1202 | Views in the last 30 days: 19
 
Related Articles
FORUM

How to Specify Length of Output Column in SELECT Stmt?

How to Specify Length of Output Column in SELECT Stmt?

FORUM

Select query ignoring certain columns

Instead writing all column name. I will specify the column that Select should ignore.

FORUM

how to add column in specified location in a table

how to add column in specified location in a table

FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

SQL Server 2005 Setup - Components to Install are grayed out

Unable to select desired components for installation

Tags
export    
import    
integration services (ssis)    
json    
 
Contribute