{"id":2626,"date":"2018-02-14T16:52:37","date_gmt":"2018-02-14T16:52:37","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=2626"},"modified":"2018-03-16T16:57:23","modified_gmt":"2018-03-16T16:57:23","slug":"convert-json-to-csv-file-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/convert-json-to-csv-file-using-ssis\/","title":{"rendered":"How to convert JSON to CSV File using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

In this article, you will learn how to convert JSON to CSV File using SSIS<\/a>. 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<\/a> together with SSIS will make it easy to happen.<\/p>\n

We will be using this\u00a0ZappySys SSIS PowerPack<\/a>\u00a0component to make things work:<\/p>\n

\n\n\n\n
\"\"<\/a><\/td>\nJSON Source (REST API or File)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Prerequisites<\/h2>\n
    \n
  1. SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. ZappySys SSIS PowerPack<\/em> installed<\/a>.<\/li>\n<\/ol>\n

    Step-by-Step\u00a0\u2013 Export JSON to CSV File with SSIS<\/span><\/h2>\n
      \n
    1. From SSIS Toolbox drag and drop Data Flow<\/em> into the Control Flow<\/em>.<\/li>\n
    2. Then drag and drop JSON Source (REST API or File)<\/em> into the Data Flow<\/em>.<\/li>\n
    3. Open JSON Source<\/em> component for modifications.<\/li>\n
    4. Load JSON directly, from variable or URL:
      \n      \"Convert<\/a><\/li>\n
    5. Use JSONPath Expression in Filter<\/em> section to select the data you need. We will use $.store.book[*]<\/span>\u00a0to extract all books from JSON array. Check\u00a0Understanding JSONPath Expression with Examples<\/a>\u00a0article to see more examples how this filter works.<\/li>\n
    6. Close the window.<\/li>\n
    7. Drag and drop Flat File Destination<\/em> into Data Flow<\/em> and connect it to JSON Source<\/em>:
      \n      \"\"<\/a><\/li>\n
    8. Open Flat File Destination<\/em> for configuration, configure it and map JSON Source<\/em> columns with Flat File Destination<\/em> columns.<\/li>\n
    9. You are done! JSON will be exported to a CSV (download a sample package<\/a>).<\/li>\n<\/ol>\n

      Using API as JSON source<\/h2>\n

      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<\/em> OAuth connection:<\/p>\n

      How to authenticate to an API with OAuth 2.0 using SSIS<\/a><\/p><\/blockquote>\n