|
SSIS Web API Destination
|
 
|
|
SSIS Web API Destination can be used to POST multiple requests to any Web API Endpoint (POST data to URL). It support advanced Authentication mechanism such as OAuth, Basic Auth. Data can be in any format such as CSV, JSON, XML for other. It also supports Batch operation so you can group multiple request in one batch.
|
https://zappysys.com/downloads/files/test/invoices.csvIn the settings tab set HTTP Request Method to GET. Check on First row has column name.
| Property Name | Description | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| LoggingMode | LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.
|
||||||||||||||||||||||||||
| PrefixTimestamp | When you enable this property it will prefix timestamp before Log messages. | ||||||||||||||||||||||||||
| HttpRequestUrl | API URL you like to call | ||||||||||||||||||||||||||
| HttpRequestMethod | Http Web Request Method (e.g. POST, GET, PUT, LIST, DELETE...). Refer your API documentation if you are not sure which method you have to use. | ||||||||||||||||||||||||||
| HttpHeaders | Set this if you want to set custom Http headers. You may use variable anywhere in the header value using syntax {{User::YourVarName}}. Syntax of Header key value pair is : <request><header><name>x-myheader-1</name><value>AAA</value></header> <header><name>x-myheader-2</name><value>BBB</value></header></request> | ||||||||||||||||||||||||||
| HttpRequestData | User defined data you wish to send along with your HTTP Request (e.g. Upload file data, Form POST data). Usually you have to set content-type of your data but if you select RequestMethod=POST then system will automatically set content-type=application/x-www-form-urlencoded. | ||||||||||||||||||||||||||
| HttpRequestContentType | Specifies content type for data you wish to POST. If you select Default option then system default content type will be used (i.e. application/x-www-form-urlencoded). If you specify Content-Type header along with this option then header value takes precedence.
|
||||||||||||||||||||||||||
| ContinueOnUrlNotFoundError | If this option is true then component will continue without exception on 404 error (Url not found). This allows you to consume data gracefully. | ||||||||||||||||||||||||||
| DisableExpect100Continue | Set this option to true to disable Expect 100-continue negotiation for POST requests (or any requests with BODY). By default client waits for server response for POST request (this is done via Expect: 100-continue header in POST request). Disable this option to improve response time significantly in some cases (Disabling this option is not recommended for larger POST requests- i.e. File Upload) | ||||||||||||||||||||||||||
| IgnoreCertificateErrors | Ignore SSL certificate related errors. Example: if you getting SSL/TLS errors because of certificate expired or certificate is not from trusted authority or certificate is self-signed. By checking this option you will not get SSL/TLS error. | ||||||||||||||||||||||||||
| AllowUnsecureSuite | Allow unsecure ciphers/suites and curves for SSLS/TLS communication. Use this option to communicate with web servers which needs legacy / unsecured ciphers support. This option is only trigged when you change default SSL/TLS Version on advanced settings tab. | ||||||||||||||||||||||||||
| EnableCompressionSupport | Enable support for gzip or deflate compression (for deflate you must turn on [Tls 1.0 Or Higher] Option on Advanced Settings - Security Protocol for HTTPS). When you check this option compressed response automatically de-compressed saving bandwidth. This option is only valid if web server supports compressed response stream. Check your API documentation for more information. | ||||||||||||||||||||||||||
| SecurityProtocol | Specifies which security protocol is supported for HTTPS communication. Using this option you can enable legacy protocol or enforce to use latest version of security protocol (Note: TLS 1.2 is only supported in SSIS 2014 or Higher).
|
||||||||||||||||||||||||||
| WaitTime | Wait time in milliseconds (Pause after each request). 0=No wait. Use this delay time if your API has limit on how many requests you can make in certain time frame. | ||||||||||||||||||||||||||
| OnErrorOutputResponseBody | When you redirect error to error output by default you get additional information in ErrorMessage column. Check this option if you need exact Response Body (Useful if its in JSON/XML format which needs to be parsed for additional information for later step). | ||||||||||||||||||||||||||
| EnableParallelRequests | Enable multiple requests using parallel threads. | ||||||||||||||||||||||||||
| MaxParallelThreads | Maximum parallel threads to use for processing multiple web requests. | ||||||||||||||||||||||||||
| EnableBodyBatch | By default one request is sent for each input row. If your API supports multiple records in a single call via HTTP Body then enable this setting. See also BodyBatchSize, BodyHeader, BodyFooter and BodyRowSeparator property. | ||||||||||||||||||||||||||
| BodyHeader | When you have to wrap input content between opening and closing tags for each call then set this property to specify opening for body content. For Example if you want to build Body like this (send max 100 rows per call) ==> { records : [ {...}, {...}, {...} ] } then you can set BatchSize=100, BodyHeader as "{ records: [" , BodyFooter as "] }" and BodyRowSeperator as comma {,}. This property is ignored if EnableBodyBatch=false. | ||||||||||||||||||||||||||
| BodyFooter | When you have to wrap input content between opening and closing tags for each call then set this property to specify closing for body content. For Example if you want to build Body like this (send max 100 rows per call) ==> { records : [ {...}, {...}, {...} ] } then you can set BatchSize=100, BodyHeader as "{ records: [" , BodyFooter as "] }" and BodyRowSeperator as comma {,}. This property is ignored if EnableBodyBatch=false. | ||||||||||||||||||||||||||
| BodyBatchSize | If your API supports multiple records via HTTP Body then change this setting > 1 to include multiple upstream records into single call. See also BodyHeader, BodyFooter and BodyRowSeparator property. This property is ignored if EnableBodyBatch=false. | ||||||||||||||||||||||||||
| BodyRowSeparator | When you submit multiple rows in a batch you can separate multiple documents / records using custom separator (e.g. in JSON Array you can use comma {,} as record separator if POST document is in array format [ {...}, {...}, {...} ] ). For last row of batch blank separator is used. | ||||||||||||||||||||||||||
| TreatResponseAsBinary | Returns response data as binary data (e.g. Byte Array). Check this option is response is Zip file, Image file or any non text data which needs to be exported as raw data without any encoding. | ||||||||||||||||||||||||||
| IsMultiPartUpload | Check this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle |
||||||||||||||||||||||||||
| AddMultiPartPostamble | Enable this option if your server is expecting (i.e. CRLF) after last Multi-Part boundary in the Body. This option is only applicable if you enabled IsMultiPartUpload and sending data in Key=value format (i.e. Mutipart). For raw file upload this option has no effect. |
||||||||||||||||||||||||||
| EnableRequestCompression | Enable this option if you like to compress request body (i.e. Content-Encoding: gzip). Enabling this option can speedup data transfer especially when you are sending large amount data (i.e. Upload file) |
|
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 […] |
|
How to batch REST API requests in SSIS (Bulk Operation)Introduction In our previous article we saw how to POST data to REST API using few different ways. Now let’s go one step further and discuss another common scenario to batch REST API requests in SSIS. For efficient data transfer many API provides you to submit multiple records in a single request. In this article […] |
|
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 CallIntroduction 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 […] |
|
SSIS Geocoding with Google Maps APIIntroduction In this tutorial, we will cover the topics of how to perform geocoding on the addresses and reverse geocoding on the location coordinates using SSIS and Google Maps API. So what is geocoding, exactly? Geocoding is the process of translating an address (e.g. a street address) or a place to coordinates on the Earth’s surface. Simply […] |
|
Read / Write Zoho CRM data using SSIS REST API CallIntroduction In this post, you will learn how to read / write Zoho CRM data using SSIS (Drag and drop approach without any coding). We will use SSIS JSON/ REST API Source to extract data from Zoho API and use SSIS Web API Destination to write data to Zoho. We will look at step-by-step instructions to read Zoho CRM […] |
|
Read / Write Smartsheet data using SSIS REST API CallIntroduction In this post you will learn how to Read / write Smartsheet data using SSIS (Drag and drop approach without any coding). We will use SSIS JSON/ REST API Source to extract data from Smartsheet API and use SSIS Web API Destination to write data to Smartsheet. This approach is similar to our previous […] |
|
Load SQL Server data to Workday using SSIS / SOAP APIIntroduction In our previous article, we saw step-by-step approach to read data from workday using SSIS. In this article, we will focus on how to load SQL Server data to Workday (e.g. POST, Create, Update). We will use SSIS Web API Destination and the combination of other Transforms such as SSIS Template Transform and SSIS XML Generator […] |
|
How to read / write data in Google BigQuery using SSISIntroduction Google BigQuery is a fully managed Big Data platform to run queries against large scale data. In this article you will learn how to integrate Google BigQuery data into Microsoft SQL Server using SSIS. We will leverage highly flexible JSON based REST API Connector and OAuth Connection to import / export data from Google […] |
|
Loading data from SQL Server to Elasticsearch with SSISIntroduction Elasticsearch is a powerful engine that allows you to store, aggregate and, most importantly, search data in a very analytical way. In this tutorial, you will learn how to bulk load data from SQL Server to Elasticsearch with SSIS (part of SQL Server) and ZappySys PowerPack. The scope of this article will be to show how to import […] |
