SSIS JSON Source - Read from REST API / Load into SQL Server Table / Import JSON File / GET from URL

Learn how to read REST API / JSON File data and load into SQL Server Table in SSIS (Using UPSERT Destination)

00:49 Read from REST API URL (GET / POST Call)
02:52 Setup API Connection - HTTP / OAuth
04:47 API Pagination
06:40 Load API data into SQL Server (UPSERT - Insert or Update)
08:28 Read from Local JSON File
09:29 Editing Columns Metadata for JSON
11:14 Read from JSON Array (nested) - Direct String
11:56 Choose Filter - (Flatten array / de-normalize nested hierarchy)

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 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory

Related Searches:
===========================================
Read from JSON File in SSIS
Load REST API data into SQL Server Table
Import REST API data into SQL Server Table
UPSERT into SQL Server
GET data from URL (API)
Read JSON in SSIS
Read API in SSIS
Read REST API in SSIS
Call REST API in SSIS