SSIS Web API Integration Pack (Call REST API, SOAP Web Service)
SSIS Web API Integration Pack is your complete toolset to automate REST API/Web API/SOAP/OAuth/OData Service integration in SSIS. Using simple drag and drop interface you can consume data from any Web Service in few clicks (XML or JSON format). You can also send Ad-hoc request to any web server including file upload requests. It includes JSON Source Connector, XML Source Connector and REST API Task.
- Consume any RESTful Web API (like this) or XML SOAP Web Service (like this) in few clicks
- Send Ad-hoc Http Web Request (e.g. GET, POST) and save response into SSIS variable or file
- Filter, Pivot, De-normalize nested XML or JSON into flat document just like regular database table
- Read data from JSON/XML files or RESTful API services (such as Twitter, Zendesk)
- Support for JSONPath style expression to extract sub-documents or array
- 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 (see)
- 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