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
Allow to cancel infinite pagination on UI Save
Option to parse output from command line Standard Output Stream (e.g. cmd:>curl -k http://httpbin.org/get)
Allow Regular Expression in Filter (e.g. $.store.books[?(@author =~ /^sam$/ )]
Support for SQL Server 2022, 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory
Click here to see articles related to JSON Source

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 easily.

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.

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

HTTP Connection Support (Token / Basic Auth)

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.

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)

Array Flattening Option

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)

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)

Articles / Tutorials

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

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

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)

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

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)

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

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

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)

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

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

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 […]