Web Request Tasks

EDIS Online User Guide – Web Tasks

EDIS can perform web requests and soap requests quick and easy. Below are examples.

If you are needing to connect to a web service with a user ID/password, use the procedure EDIS.usp_config_web_svc_id to setup a connection.

Web Service Connection Setup
Procedure: SSISDB.EDIS.usp_config_web_svc_id

Parameters

Name Description Default Value
@svc_id Service ID alias to use to connect to a web service at runtime None; Required
@uid The user ID to use to connect to the web service None; Required
@pwd The password associated with the User ID to connect to the web service None; Required

Example: This example creates a new web service connection with alias “SVC_WEATHER_FCST”.

EXEC SSISDB.EDIS.usp_config_web_svc_id

 @svc_id = 'SVC_WEATHER_FCST'

,@uid = 'admin@weather_svc_example.com'

,@pwd = '_svc_weather_x1nc!#sg1'

 

Web Request Task
The web request task will calls a webrequest using a URL to a REST API service on the web. You can use this task to do things such as retrieve weather forecast information, department of energy fuel data, and other data sets that interface through OData.

Procedure: SSISDB.EDIS.usp_run_web_request

Parameters

Name Description Default Value
@url The URL that has the OData REST api query None; Required
@output_tbl_nm Global temporary table that gets created when the web request runs. The results of the OData call are loaded to this table as a single row varchar(max) None; Required
@encoding The encoding to use to consume the odata call. Supported formats are utf7, utf8, utf16, utf32, Unicode, bigendian, and asci Utf16
@content_type content type argument for the web request Empty String
@accept The accept header for the web request. Empty String
@timeout The time in seconds you want the request to run before it cancels due to a timeout 100 seconds (100000 milliseconds)
@svc_id If the web service call needs to login using credentials, setup a service ID using proc SSISDB.EDIS.usp_config_web_svc_id. Then use that service ID for the web request with this parameter Empty String
@header_tmp_tbl Allows you to load 1 or more header key/value pairs to submit for the web request. You first create a temp table with two columns and then load in your header key/value pairs to it. When EIDS reads it in, it assumes that column 1 on your temp table is the header key, and that column 2 is the header value. Empty table
@token_auth_url If you need to connect to a web service using token based authentication i.e. get a token key and pass in “bearer {KEY}”, specify the token authentication URL here and add the authorization code to the @header_tmp_tbl var Empty String
@tls_version Indicator for the version of TLS to use. Supported values are 1.1 and 1.2; if not specified, it will use TLS 1.0 1.0
@method Method to call for the web request. Currently supports “GET” only GET
@proxy_svc_id If you need to use a proxy account to run the web request, setup a service ID for the proxy using proc SSISDB.EDIS.usp_config_web_svc_id. This service ID should be treated differently from the @svc_id parameter, which is used to login to the web service itself. Empty String
@proxy_url If accessing the web request with a proxy, provide the proxy URL in this parameter Empty String
@proxy_port If you need to use a proxy account to run the web request, specify the proxy port here if applicable. NULL

Examples

Example 1: Querying Yahoo weather for the forecast in Seattle.

 

declare @weather_qry_url varchar(1000) =

'

https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%20in%20(select%20woeid%20from%20geo.places(1)%20where%20text%3D%22seattle%2C%20wa%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

'

exec SSISDB.EDIS.usp_run_web_request

@url = @weather_qry_url

,@output_tbl_nm = '##seattle_weather'

;

select *

from ##seattle_weather

 

Example 2: Querying a service and adding header info and using token based authentication.

 

use SSISDB

go

create table #headers (header_nm nvarchar(250), header_val nvarchar(4000));

insert #headers values ('Authorization', 'basic:abcd1234')

Exec SSISDB.EDIS.usp_run_web_request

@url = 'http://services.contoso.com/reports/clients/1234/daily'

,@output_tbl_nm = '##results'

,@header_tmp_tbl = '#headers'

,@token_auth_url = 'http://services.contoso.com/auth'

 

Soap Request Task
The soap request task runs a soap request and gets the corresponding results loaded to a global temp table.

Procedure: SSISDB.EDIS.usp_run_soap_request

Parameters

Name Description Default Value
@svc_id If the soap reqeust call needs to login using credentials, setup a service ID using proc SSISDB.EDIS.usp_config_web_svc_id Then use that service ID for the web request with this parameter Empty String
@url URL to connect to for soap request None; Required
@soap_envelope The soap envelope to pass None; Required
@timeout The time in seconds you want the request to run before it cancels due to a timeout 100 seconds (100000 milliseconds)
@output_tbl_nm Output table to load results to None; Required
@encoding The encoding to use to consume the soap call. Supported formats are utf7, utf8, utf16, utf32, Unicode, bigendian, and asci UTF16
@content_type The content type for the soap call. text/xml;charset=UTF-16

Examples

Example 1: Using a Soap call for Sales Force Company update.

 

declare @soap_env varchar(max) =

'

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="<a rel="nofollow" class="external free" href="http://schemas.xmlsoap.org/soap/envelope/">
http://schemas.xmlsoap.org/soap/envelope/</a>"
xmlns:urn="urn:enterprise.soap.sforce.com"
xmlns:urn1="urn:sobject.enterprise.soap.sforce.com"
xmlns:xsi="<a rel="nofollow" class="external free"
href="http://www.w3.org/2001/XMLSchema-instance">
http://www.w3.org/2001/XMLSchema-instance</a>">
<soapenv:Header>
<urn:SessionHeader> <urn:sessionId><b>QwWsHJyTPW.1pd0_jXlNKOSU</b></urn:sessionId>
</urn:SessionHeader>
</soapenv:Header>
<soapenv:Body>
<urn:update>
<urn:sObjects xsi:type="urn1:Account"> <!--Zero or more repetitions:-->
<urn1:fieldsToNull><b>NumberOfEmployees</b></urn1:fieldsToNull>
<urn1:fieldsToNull><b>Fax</b></urn1:fieldsToNull>
<urn1:Id><b>001D000000HTK3aIAH</b></urn1:Id>
<Name><b>Acme Rocket Supply, Inc.</b></Name>
</urn:sObjects>
</urn:update>
</soapenv:Body>
</soapenv:Envelope>

'

exec SSISDB.EDIS.usp_run_soap_request

@url = 'https://na1.salesforce.com/services/Soap/c/10.0'

,@soap_env = @soap_env

,@output_tbl_nm = '##comp_update_res'

,@encoding = 'utf-8'

,@content_type = 'text/xml;charset=UTF-8'

,@timeout = 120000

;