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

SSIS REST API Source (JSON Connector)

SSIS REST API Source (JSON Source) can be used to extract and output JSON data from REST API (Web URL), JSON files or direct JSON string (direct value, variables or database column). This REST API component also supports JSONPath to filter data from nested array/sub-documents. This component is optimized to work with very large files. If you are consuming XML Based format then check this component instead.

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..)
  • Inbuilt support for OData format (Read more)
  • Authenticate using Basic authorization (Passing UserID and Password to REST API Service)
  • Support for looping through multiple files using wildcard pattern (e.g. *.json).
  • Support for HTTP Request with Custom Headers.
  • Support for REST API Paging (3 different methods) – 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
  • Support for SSL3 and TLS 1.2 protocol for encryption
  • Support for passing cookies
  • Support for Proxy
  • 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 Parser Transform

See also
SSIS Web API Destination - Send data to API Web API Destination
Custom SSIS Tasks - Export JSON File Task Export JSON File Task
Custom SSIS Tasks - Call REST API Webservice (GET, POST, DELETE etc) REST API Webservice Task
Custom SSIS Tasks - XML Source Connector (File, SOAP, REST API) XML Source Connector (File, SOAP, REST API)
Download Help File Buy
View All Tasks

Featured Articles
ScreenshotsUseful LinksSystem Requirements

REST 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 REST API Source (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 SSIS REST API Source (JSON Source).

JSON 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 REST API Source (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.

 

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

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

SSIS REST API Source (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 REST API Source (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 REST API Source (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.

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)

 

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

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

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

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

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

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

 

How to read Twitter data in SSIS using REST API Call

Click here to learn more about System Requirements
Download View All Tasks Like This