SSIS Json Source (File, REST API, OData) - Extract, Parse, Output

SSIS JSON Source (File, REST API, OData)

JSON Source Connector can be used to extract and output JSON data stored in local JSON files, JSON data coming from REST API web service calls (Web URL) or direct JSON String (variables or DB columns). JSON component also supports JSONPath to filter data from nested array/sub-documents. This component is optimized to work with very large files.

Features

  • Read data from JSON files or RESTful API services (such as Twitter, Zendesk)
  • De-normalize nested JSON into a flat document just like a regular database table
  • The unparalleled performance with large file support (See how we processed 3 million rows in 3 mins)
  • Extract nested hierarchy/array using JSONPath expression (see also: Extract multiple arrays using JSON Parser Transform)
  • Support for OAuth 1.0 and 2.0 security standard which is adopted by major cloud services (e.g. Google API, Facebook, Salesforce, Dynamics CRM, Office 365, MailChimp and many more..)
  • Call OData format (Read more)
  • Call Amazon AWS API (e.g. EC2, API Gateway, Lambda and more…)
  • Call API using Dynamic Token Authenticate or Basic authorization (i.e. UserID / Password)
  • Looping through multiple files using wildcard pattern (e.g. *.json).
  • Support for Array Flattening (Child Array Items as Columns)
  • HTTP Request with Custom Headers.
  • Support for HMAC Authentication (SHA256, SHA512, MD5) – Allow APIs like Azure CosmosDB / DocumentDB
  • REST API Pagination Support – Loop through multiple requests (explained here and here)
  • Support for 2D arrays and Complex Transformation (See this article and this for Google BigQuery)
  • Support for Pivoting (Convert Columns to Rows)
  • Ability to output and indent raw JSON from inner array
  • HTTPS / TLS 1.2 Support for more secure communication
  • Support for passing cookies
  • Support for Proxy
  • Support for SQL Server 2017, 2016, 2014, 2012, 2008 (32/64 bit) and now Azure Data Factory
  • Click here to see articles related to JSON Parser Transform

Download Help File Buy
View All Tasks

Featured Articles

ScreenshotsUseful LinksSystem Requirements

File Mode: Loading JSON Files to SQL table, Use Wildcard for reading multiple JSON files

Read Json File data from Web Url Example in SSIS

SSIS JSON Source supports of reading data from JSON Files (single or multiple). You can also use wildcard pattern to read from multiple files with similar structure. It also allows you to preview sample data very esaily.

 

API Mode: Loading REST API data to SQL table (Specify URL, HTTP Method, Headers, Body)

Load JSON data directly from REST API service, Pass HTTP Headers (Use POST/GET etc)

SSIS JSON Source supports reading data from REST API and output as SQL Table. You can also specify many advanced options such as credentials, pagination parameters, HTTP Method (i.e. GET, POST), HTTP headers, request body and many more. By tweaking these options you can virtually call any REST API using JSON source.

Direct String Mode: Reading JSON string to SQL Table (Syntax Highlighting, Preview, filter)

Preview JSON in table format, extract and filter subset using JSONPath expression

SSIS JSON Source also supports reading direct JSON string. It also allows you to read JSON stored in SSIS Variable.

Using JSONPath to extract / de-normalize nested hierarchy (Extract data from Array)

SSIS JSON Source supports powerful feature to visualize the data structure of your JSON file or REST API. You can select any hierarchy you like to extract using select filter UI. Notice that JSON array is indicated using the different icon.

SSIS JSON Source supports powerful feature to visualize the data structure of your JSON file or REST API. You can select any hierarchy you like to extract using select filter UI. Notice that JSON array is indicated using the different icon.

OAuth Connection Support (OAuth 1.0 / 2.0)

Using SSIS OAuth Connection Manager in JSON Source - Read data from REST API via OAuth 1.0 or OAuth 2.0

Using SSIS OAuth Connection Manager in JSON Source – Read data from REST API via OAuth 1.0 or OAuth 2.0

HTTP Connection Support (Token / Basic Auth)

SSIS HTTP Connection Manager - Authenticate using UserID / Password (Basic Authorization)

SSIS HTTP Connection Manager – Authenticate using UserID / Password (Basic Authorization)

REST API Pagination Options

REST API Pagination Options - Paginate via URL Parameter, Paginate via Body Attribute, Paginate via HTTP Header value and more. Along with different pagination mode you will also find many options to detect pagination end condition.

REST API Pagination Options – Paginate via URL Parameter, Paginate via Body Attribute, Paginate via HTTP Header value and more. Along with different pagination mode you will also find many options to detect pagination end condition.

Select JSON Output columns, Edit SSIS Metadata for JSON Output

Advanced SSIS Metadata Editor with many features - Auto detect columns and datatype. Option to change scan row count. Select Columns for output, option to lock manual changes (prevent overwrite)

Advanced SSIS Metadata Editor with many features – Auto detect columns and datatype. Option to change scan row count. Select Columns for output, option to lock manual changes (prevent overwrite)

Array Flattening Option

Array Flattening Option - De-normalize array items into columns

Array Flattening Option – De-normalize array items into columns

Extract Multiple Outputs from JSON (using JSON Source and JSON Parser Transform)

Extract multiple array output from JSON file / REST API response in (SSIS JSON Source, JSON Parser Transform)

Extract multiple array output from JSON file / REST API response in (SSIS JSON Source, JSON Parser Transform)

SSIS Example: Loading JSON API to SQL Table with Error Handling

SSIS JSON Source Example - Loading JSON to SQL Table with Error Output (Redirect bad records to Error Output)

SSIS JSON Source Example – Loading JSON to SQL Table with Error Output (Redirect bad records to Error Output)

Articles


Click here to see all articles for [SSIS JSON Source (File/REST)] category


How to read YouTube API data in SSIS (Videos, Channels, Playlists)

How to read YouTube API data in SSIS (Videos, Channels, Playlists)

Introduction In last few articles we saw how to read data from various Google Services. In this article we will see how to read YouTube API data in SSIS. This blog mainly focus on SSIS approach but steps mentioned to call Google APIs can be useful for any developer regardless which programming language or toolset […]


Read Freshdesk data in SSIS – REST API Call

Read Freshdesk data in SSIS – REST API Call

Introduction In this post we will learn how to read Freshdesk data in SSIS using ZappySys JSON / REST API Source. We will see how to authenticate, paginate / parse and load Freshdesk data into SQL Server Table in just few clicks. If you don’t use Freshdesk and considering using Zendesk instead then check our previous […]


Create Excel File in SSIS (Read from JSON / XML)

Create Excel File in SSIS (Read from JSON / XML)

Introduction In this post, we will learn how to Create Excel File in SSIS from source like JSON / XML.  We will use SSIS PowerPack to connect and query a JSON or XML file. This article also covers creating Excel from JSON File. JSON stands for JavaScript Object Notation and it is an Open and Standard format to […]


Load data from Exact Online into SQL Server using SSIS

Load data from Exact Online into SQL Server using SSIS

Introduction In this article, you will learn how to load data from Exact Online into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. […]


Read / Write Shopify data in SSIS (REST API)

Read / Write Shopify data in SSIS (REST API)

Introduction In our previous article, we saw how to call REST API in SSIS. Now in let’s use that knowledge and learn how to read/write Shopify data in SSIS. If you are not aware of Shopify then its one of the most popular eCommerce platforms out there for small shops who sell online. Shopify provides […]


SSIS Magento data Read / Write using REST API Call

SSIS Magento data Read / Write using REST API Call

Introduction In this post we will lean SSIS Magento data read / write operations. Magento is a very popular eCommerce platform and they offer JSON based REST API and XML based SOAP API. You can use either API based on your need to automate common integration needs. We recommend using REST API (JSON API) if possible […]


How to read data from Splunk in SSIS

How to read data from Splunk in SSIS

Introduction Splunk is commonly used for searching, monitoring, and analyzing machine-generated big data, via a Web-style interface. In this post, you will learn how to implement Splunk API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS in a few clicks. We will use SSIS XML Source Connector to Read data from Splunk and Load into SQL Server / […]


Read Salesforce Marketing Cloud data in SSIS (ExactTarget API)

Read Salesforce Marketing Cloud data in SSIS (ExactTarget API)

Introduction In this post we will learn how to call Salesforce Marketing Cloud API using SSIS and load into SQL Server. We will show you use case of SSIS REST API Task  and SSIS JSON Source Basically there are two steps to call Salesforce Marketing Cloud API Obtain ClientID and ClientSecret ( Check these steps ) Get Access Token by […]


Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Introduction In this blog, we will learn How to read Visual Studio Team Services data in SSIS and load into SQL Server Table, along with few other topics such as. How to register an OAuth App for Visual studio Team Service – TFS Online (Team Foundation Server) REST API Call How to read all Issues data […]


How to read Constant Contact data in SSIS – Call REST API / Load to SQL Server

How to read Constant Contact data in SSIS – Call REST API / Load to SQL Server

Introduction   In this blog, we will learn How to read Constant Contact data in SSIS and load into SQL Server Table, along with few other topics such as how to generate an API Token using Mashery Developer Account for Constant Contact REST API Call, how to read all Contacts from Constant Contact with SSIS and […]


 

  • .net framework 3.5 or higher must be installed
  • SSIS Runtime: To execute SSIS package you will need one or more versions of SQL Server Integration Services 2005/2008/2008 R2/2012/2014 or 2016
  • To design SSIS 2012 package SSDT (VS 2010 Shell – SQL Server Data Tools) must be installed.
  • To design SSIS 2014 package SSDT-BI (VS 2013 Shell – SQL Server Data Tools) must be installed.
  • To design SSIS 2016 package SSDT-BI (VS 2015 Shell – SQL Server Data Tools) must be installed.
  • To design SSIS 2005, 2008, 2008 R2 packages BIDS (VS 2005,2008 Shell – Business Intelligence Development Studio) must be installed.
  • Supported Client Operating Systems: Windows 7, Windows 8, Windows 8.1
  • Supported Server Operating Systems: Windows Server 2003/R2, Windows Server 2008/R2, Windows Server 2012/R2
Download View All Tasks Like This