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

SSIS JSON Source (File, REST API, OData Connector)

JSON Source Connector can be used to extract and output JSON data stored in local JSON files, JSON data coming from REST API webservice calls (Web URL) or direct JSON String. 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 flat document just like regular database table
  • Support for JSONPath expression to extract sub-documents or array (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..)
  • Support for OData format (Read more)
  • Support for Basic authorization (Passing UserID and Password to REST API Service)
  • Support for looping through multiple files using wild card 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 very large JSON files
  • 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 2005, 2008, 2012, 2014, 2016 (32 bit and 64 bit)
  • Click here to see articles related to JSON Parser Transform

Download Help File Buy
View All Tasks
ScreenshotsUseful LinksSystem Requirements

De-normalize nested JSON like normal table, Pattern support to load multiple JSON files

Read Json File data from Web Url Example in SSIS

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

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

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

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

Select JSON Output columns, Edit SSIS Metadata for JSON Output

Select JSON Output columns, Edit Metadata for JSON Output

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)

Extract JSON data in SSIS dataflow using JSON Source

Extract JSON data in SSIS dataflow using JSON Source

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