Documentation forTask Factory

Task Factory REST

 Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.

REST Connection Manager (Basic)

Used with the Rest Source and Rest Destination data flow components, and the Rest Task control flow component.

Basic Authentication Settings

Task Factory Rest Connection Manager Basic Authentication Settings tab

Option Description
Configuration File The file path to the configuration file. The configuration file can be used to auto-populate the Rest Source and Destination's endpoint URLs. Select the drop-down to view all config files currently in the repository.

The repository's location is C:\Program Files (x86)\Pragmatic Works\Task Factory\OAuth2ConfigFiles. You can add new config files to this location which appear in the dropdown.

Note:  For config files that exist in a different location, select the ellipsis to launch a file explorer window to navigate to the file.
Skip Authentication Option for endpoints that do not require authentication.

Note:  Open the component properties to configure this option.
User Name The username needed to authenticate to the endpoint URL.
Password The password that allows you to authenticate to the endpoint URL.
Use Base 64 Encoded Authentication Header Select when Base 64 Encoded Authentication is used by an endpoint. This option takes your username and password delimited with a colon ( : ), and Base 64 encodes them. This encoded value is then sent as a header.

Note:  If you've configured your API with Postman, this option is configured automatically.
Security Protocol The Security Protocol used by the API.  The Default option uses the TLS version used by your .NET version by default. See the following chart for more information:
.NET VersionTLS Support Information
.NET 4.6 and aboveSupports TLS 1.2 by default.
 .NET 4.5

TLS 1.2 is supported, but you need to opt-in to use it. Execute the following code before making a connection to a secured resource to make TLS 1.2 the default :

  • ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
.NET 4.0TLS 1.2 is not supported. If you have .NET 4.5 (or above) installed on the system, you can opt in for TLS 1.2 even if your application framework doesn’t support it. SecurityProtocolType in .NET 4.0 doesn’t have an entry for TLS1.2, so you need to use a numerical representation of this enum value:
  • ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
.NET 3.5 or belowTLS 1.2 is not supported, and there is no workaround. Upgrade your application to a more recent version of the framework.
Base Endpoint URL The endpoint url for your REST connection.

Note:  This setting is optional.

Authentication Endpoint

Task Factory Rest Connection Manager Authentication Endpoint (optional) tab

Important:  If you aren't getting an access token from your API, you don't need to configure this tab.
Note:  Some endpoints may require a successful authentication token every time they are accessed. You can dynamically set this token using a variable by applying the following format: <Namespace::VariableName> Example:<@User::SuccessToken>

For information about setting a dynamic token as a bearer token for your Rest Source, see the following support article.

Option Description
Rest Authentication Endpoint Url The endpoint that returns an access token. For example, In this field, you should enter the URL of the website/ web service that you want to connect to.

Note:  Your token endpoint will typically end in token, for example google.com/api/token
Results Returned In The Rest Source allows you to connect to endpoints with JSON and XML formats. This selection determines which format the component uses.
Successful Authentication Token The token path of the token that you want to get. For example, if your response looks like :
{
"access_token": "123",
"refresh_token":"456"
}
Your successful Authentication will be access_token.

Headers

Task Factory Rest Connection Manager Headers tab

Option Description
Add New Header Select to add a new header name and value. Once added, you can select within the column and configure the name and the value.
Remove Header Removes the selected header to be included in the component's output.

Proxy

Task Factory Rest Connection Manager Proxy tab

Option Description
Proxy Host When connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy Port The port number that corresponds to the URL proxy host.
User Name The username needed to authenticate to the proxy.
Password The password that allows you to authenticate to the proxy.

Advanced Options

Task Factory Rest Connection Manager Advanced Options

Option Description
Ignore Ssl Certificate Verification Selecting this option ignores the SSL Certificate notification from the API during the connection.

Note:  This option applies to APIs that require you to have an SSL Certificate. Select this option if the SSL Certificate is not used. 
Follow Redirects Selecting this option implements a 301 redirect on configured endpoints. For example, this option would automatically send your request to an HTTPS redirect once the request has reached the http version of the site.  
Important:  You need to register a new app with the service being used to obtain API Keys and Secrets. This can be done by visiting the service's developer website. 

REST OAuth Connection Manager

Used with the Rest Source and Rest Destination data flow components, and the Rest Task control flow component.

Connection Settings

Task Factory Rest OAuth Connection Manager Connection Settings tab

Option Description
Configuration File The file path to the configuration file. The configuration file can be used to auto-populate the Rest Source and Destination's endpoint URL's. Select the down arrow to view all config files currently in the repository.

The repository's location is C:\Program Files (x86)\Pragmatic Works\Task Factory\OAuth2ConfigFiles. You can add new config files to this location which appear in the dropdown.

Note:  For config files that exist in a different location, select the ellipsis to launch a file explorer window to navigate to the file. 
Api Key, API Secret, Token, and Token Secret Authentication information provided to you at the application's developer site.

Note:  The Token field can be left empty for API's that do not require one.
Signature Type Identifies the hash algorithm used in the vendor's authentication process.
Realm and Verifier Optional properties supplied by the vendor that the connection is authenticating with.
Security Protocol The Security Protocol used by the API.  The Default option uses the TLS version used by your .NET version by default. See the following chart for more information:
.NET VersionTLS Support Information
.NET 4.6 and aboveSupports TLS 1.2 by default.
 .NET 4.5

TLS 1.2 is supported, but you need to opt-in to use it. Execute the following code before making a connection to a secured resource to make TLS 1.2 the default:

  • ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
.NET 4.0TLS 1.2 is not supported. If you have .NET 4.5 (or above) installed on the system, you can opt in for TLS 1.2 even if your application framework doesn’t support it. SecurityProtocolType in .NET 4.0 doesn’t have an entry for TLS1.2, so you need to use a numerical representation of this enum value:
  • ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
.NET 3.5 or belowTLS 1.2 is not supported, and there is no workaround. Upgrade your application to a more recent version of the framework.

Headers

Task Factory Rest OAuth Connection Manager Headers tab

Option Description
Add New Header Select to add a new header name and value. Once added, you can select within the column and configure the name and the value.
Remove Header Remove the selected header to be included in the component's output.

Proxy

Task Factory OAuth Connection Manager Proxy

Option Description
Proxy Host When connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy Port The port number that corresponds to the URL proxy host.
User Name The username needed to authenticate to the proxy.
Password The password that allows you to authenticate to the proxy.

Advanced Options

Task Factory Rest OAuth Connection Manager Advanced Options

Option Description
Ignore Ssl Certificate Verification Selecting this option ignores the SSL Certificate notification from the API during the connection.

Note:  This option applies to APIs that require you to have an SSL Certificate. Select this option if the SSL Certificate is not needed.
Follow Redirects Selecting this option implements a 301 redirect on configured endpoints. For example, this option would automatically send your request to an HTTPS redirect once the request has reached the HTTP version of the site.  
Important:  You need to register a new app with the service being used to obtain API Keys and Secrets. This can be done by visiting the service's developer website. For more information about creating and using OAuth2 connections, see OAuth2 Simplified.

REST OAuth2 Connection Manager

Used with the Rest Source and Rest Destination data flow components, and the Rest Task control flow component.

Connection Settings

Task Factory Rest OAuth2 Connection Manager Connection Settings tab

Option Description
Configuration File You can create and use configuration files to setup the OAuth2 connections. Select the down arrow to view all config files currently in the repository.

The repository's location is C:\%PROGRAMFILES(X86)%\SentryOne\Task Factory\OAuth2ConfigFiles. You can add new config files to this location which appear in the drop-down.

Note:  For config files that exist in a different location, select the ellipsis to launch a file explorer window to navigate to the file.

Additional Information: For a tutorial explaining configuration files for the Rest OAuth2 connection manager, see the following video.
Api Key and Api Secret Authentication information provided to users at the application's developer site.
Access Token To obtain this information if you do not already have it, select the Get Token button and complete the information requested in the Token Getter user interface.
Is Bearer Token Select this option if the Access Token is a bearer token.
Access Token Expiration Date The expiration date of the access token.

Note:  This settings is optional.
Security Protocol The Security Protocol used by the API.  The Default option uses the TLS version used by your .NET version by default. See the following chart for more information :
.NET VersionTLS Support Information
.NET 4.6 and aboveSupports TLS 1.2 by default.
 .NET 4.5

TLS 1.2 is supported, but you need to opt-in to use it. Execute the following code before making a connection to a secured resource to make TLS 1.2 the default :

  • ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
.NET 4.0TLS 1.2 is not supported. If you have .NET 4.5 (or above) installed on the system, you can opt in for TLS 1.2 even if your application framework doesn’t support it. SecurityProtocolType in .NET 4.0 doesn’t have an entry for TLS1.2, so you need to use a numerical representation of this enum value:
  • ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
.NET 3.5 or belowTLS 1.2 is not supported, and there is no workaround. Upgrade your application to a more recent version of the framework.

Token Getter (opened when Get Token button is used.)

Task Factory OAuth2 Token Getter

Note:  Please see your service's online documentation to assist in retrieving the information below.
Option Description
Choose Settings File You can select pre-configured settings for several popular services including, but not limited to: Google, GoToMeeting, and PayPal Sandbox.

Note:  You will use .oauth files for this option, which can be found in the following directory C:\%PROGRAMFILES(X86)%\SentryOne\Task Factory\OAuth2ConfigFiles 
Client / Api Key Authentication information provided to you at the application's developer site.

Note:  This should match the API Key from the Connection Settings window.
Client / Api Secret Authentication information provided to you at the application's developer site.

Note:  This should match the API Secret from the Connection Settings window.
Authorization Request Url The URL used to request authorization from the service.

Example: https://api.citrixonline.com/oauth/authorize?client_id={ApiKey})
 Access Token Request Url The Url used to request an access token from the service.

Example: https://api.citrixonline.com/oauth/access_token
Use Code Challenge Select to use a challenge code.
Code Challenge Method Select the available challenge code method: Plain or S256.
Resource The server that will handle the authenticated request.
Include Nonce Select to include nonce.
Nonce Length Enter the desired nonce length.
Response Mode The format of the request that's required for the call.
Response Type The type of the request that's required for the call.
Scopes The list of permissions that you want to use in the call.
Listen Port The port on your local machine where you want to send the token request response.
Your Redirect Uri Use this as your Redirect Uri in your OAuth2 App settings.
Use Basic Authentication Select to use Basic Authentication. 
Get Access Token Selecting this button opens the (configured) service's online authentication screen in the window below. To complete the process, enter the credentials of the account used to access the service. If successful, the Token Getter window closes and you return to the Connection Settings window.

Note:  Bad requests should redirect to a URL configured either in the application or the access token request URL. You can also copy the URL to help troubleshoot invalid requests.

Headers

You can create header names and values by entering the information in their corresponding windows.

Task Factory Rest OAuth2 Connection Manager Headers tab

Refresh Token (Results Returned In JSON / XML)

Task Factory Rest OAuth2 Connection Manager Refresh Token Json

Note:  When you get an access token manually from the get token button or you've copied it from your service, it only does this once which could cause potential failures in your package. You need to have a refresh token setup to get subsequent tokens in your package. 
Additional Information: For information about OAuth 2.0 grants, see the following guide.
Option Description
Refresh Token Authentication information provided to you at the application's developer site.

Note:  This should match the API Key from the Connection Settings window.
Token Request Url The URL that returns a refresh token. Example: https://api.citrixonline.com/oauth/access_token
Headers Allows you to add headers within the refresh token. Select Add New Header to add a header. Select Remove header to remove a header.
Results Returned In Specifies how results are returned from the application (JSON, XML, or String.)
Access Token Path The token path for the access token. Example: access_token
Refresh Token Path The token path for the refresh token. Example: refresh_token
Use Token Store This option creates a file on your machine that saves the access token, and refresh token in an encrypted format.
Token Store Id Identifies the Token Store Id.
Token Store Path (Optional) Identifies the Token Store path.
Test Get Refresh Token Select this option to test the retrieval of the Refresh Token. 

Refresh Token (Results Returned In String)

Task Factory Rest OAuth2 Connection Manager Refresh Token tab String

Option Description
Refresh Token Authentication information provided to you at the application's developer site.

Note:  This should match the API Key from the Connection Settings window.
Token Request Url The URL that returns a refresh token. Example: https://api.citrixonline.com/oauth/access_token
Headers Allows you to add headers within the refresh token. Select Add New Header to add a header. Select Remove header to remove a header.
Results Returned In Specifies how results are returned from the application (JSON or XML.)
Access Token Path The token path for the access token. Example: access_token
Refresh Token Path The token path for the refresh token. Example: refresh_token
Group Index Identifies the index position to return (0 based).
Use Token Store This option creates a file on your machine that saves the access token, and refresh token in an encrypted format.
Token Store Id Identifies the Token Store Id.
Token Store Path (Optional) Identifies the Token Store path.
Test Get Refresh Token Select this option to test the retrieval of the Refresh Token.

Client Certificate

Task Factory Rest OAuth2 Connection Manager Client Certificate

Option Description
Use Client Certificate Select this option if you want to use the client certificate.
Certificate Store Location The client store location. 
Search For Certificate Enter a certificate that you want to search for and then select Find.

Note:  Leave this section blank and select Find to view all certificates.
Certificate Thumbprint The identifier thumbprint of the selected certificate.

Proxy

Task Factory Rest OAuth2 Connection Manager Proxy tab

Option Description
Proxy Host When connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy Port The port number that corresponds to the URL proxy host.
User Name The username needed to authenticate to the proxy.
Password The password that allows you to authenticate to the proxy.

Advanced Options

Task Factory Rest OAuth2 Connection Manager Advanced Options

Option Description
Ignore Ssl Certificate Verification Selecting this option ignores the SSL Certificate notification from the API during the connection.

Note:  This option applies to APIs that require you to have an SSL Certificate. Select this option if the SSL Certificate is not needed.
Follow Redirects Selecting this option implements a 301 redirect on configured endpoints. For example, this option would automatically send your request to an HTTPS redirect once the request has reached the HTTP version of the site.  

Configuration Files

Additional Information: Customize the configuration files to fit your needs. For a full list of the configuration files for Task Factory Rest connection managers, see the Task Factory GitHub.

You can choose to create or select a configuration file to help you configure your OAuth2 connection manager. Select a link to download the corresponding config file.


Select a configuration file to configure your OAuth2 Connection Manager by completing the following steps:

  1. Select the ellipsis  to open the file explorer. Navigate to the .config file and select OK to open the OAuth2 Token Getter. Note:  You can also select an applicable Configuration File from the drop-down menu.
    Task Factory Rest OAuth2 Connection Manager select ellipsis
  2. Enter your access token configuration parameters in the OAuth2 Token Getter.  Select Get Access Token, to open a sign-in window, and enter the application's credentials. Note:  The Task Factory OAuth2 Token Getter will require different Access Token Configuration Parameters based on your configuration file selection. This example displays the Google Drive OAuth2 Token Getter requirements.
    Task Factory OAuth2 Token Getter Get Access Token
  3. After permission is granted, the OAuth2 Token Getter window closes and the Refresh Token tab populates with the required information. Below is an example of Google Analytics. (The actual Refresh Token has been deleted for security.) Select OK to close the Connection Manager. Task Factory Rest OAuth2 Connection Manager populated

REST Source

Source Icon Source Description
Task Factory Rest Source Icon The REST Source allows you to connect to a web service that utilizes a REST API and extracts data in XML or JSON format. See the Rest Source Connection Manager, Rest Source OAuth Connection Manager, and the Rest Source OAuth2 Connection Manager to learn more about configuring the component's connection manager.

API Endpoint / Output Columns

Task Factory Rest Source Api Endpoint

Option Description
Endpoint URL In this field, you set the URL of the web service. You should also choose whether you're making a call to receive information (Get), or making a request to the service (Post).

To add a user variable as a parameter to the API Endpoint URL, use the following syntax: <@Namespace::VariableName> or <@Namespace::ParameterName> 

Note:  The angle brackets (<>) and the commercial at (@) are required in the syntax. 

Examples


  • Most variables created in an SSIS package will be a part of the User namespace:
<@User::VariableName>
  • System variables are accessible using this syntax:
<@System::StartTime>
 
  • Both project and package-level parameters are accessible from their respective namespaces:
<@$Package::ParameterName>
<@$Project::ParameterName>

Variable or Parameter Name Examples

The last part of the syntax will be the variable or parameter name. The syntax is case-sensitive to the name of the variable or parameter. For example, to replace the API Endpoint URL with a variable or parameter named str_URL:

  • If the syntax should be replaced by a user created variable, the syntax should be:
<@User::str_URL>
  • If the replacement should come from a user created package parameter, the syntax should be:
<@$Package::str_URL>
  • If the replacement should come from a user created project parameter, the syntax should be:
<@$Project::str_URL>
Post/Put/Delete/Patch  Data You set the URL of the web service and choose the method making the call:
  • Post - (Default) Request that the destination URI perform an action with the provided data.
  • Get - Retrieves data from the destination
  • Put - Stores data at a URI and can be used to create a new entity or update an existing one.
  • Delete - Requests that data be removed
  • Patch - Requests an update to the specified fields of an entity at a URI.
Note:  See your application's API page to determine the appropriate action

Additional Information: For more information about these Rest options, see the following guide.
Content Type Identifies the type of content that is requested from the API.
Results Returned As Selects the format for the results:  XML, String, or JSON.

JSON, String, and XML Properties

JSON Properties Configuration 

Task Factory Rest Source Json Properties

Option Description
Root JSON Path JSON queries can return multiple levels, therefore, this field Identifies the root to be used.
Output Columns In this window, you can add and remove columns, define their name, token path (defined within the raw data), data type, length, precision, scale, and code page.

Using Wildcards (*)

The Rest Source allows for wildcards when arrays are returned. Example: Using the JSON Results below, the Root JSON Path would be results[0].appInventory[*]. This information tells the component to use the array found at position 0. Because some child arrays do not have an object name, a wildcard or can be used to return all child array objects.

{
   "results": [
       {
           "appInventory": [
               {
                   "name": "Sample name 1",
                   "identifier": "123",
               },
               {
                   "name": "Sample name 2",
                   "identifier": "456",
               },
               {
                   "name": "Sample name 3",
                   "identifier": "789",
               }
                               ]
               }
       ]
}
Important:  Columns that use DT_STR and DT_WSTR return empty string values when the data returns no value. Columns that contain other data types convert the empty string values to NULL.

XML Properties Configuration

Task Factory Rest Source Xml Properties

Important:  Columns that use DT_STR and DT_WSTR return empty string values when the data returns no value. Columns that contain other data types convert the empty string values to NULL.
Option Description
Root XPath Query XML and JSON queries can return multiple levels, therefore, this field Identifies the root to be used.
Output Columns In this window, you can add and remove columns, define their name, token path (defined within the raw data), data type, length, precision, scale, and code page.

String Configuration Properties

Task Factory Rest Source String Properties

Important:  Columns that use DT_STR and DT_WSTR return empty string values when the data returns no value. Columns that contain other data types convert the empty string values to NULL.
Option Description
RegEx Parse Pattern The regular expression used to parse the returned JSON. Leaving this field blank returns all data from the index and only one row with this information returns.
Output Columns In this window, you can add and remove columns, define their name, token path (defined within the raw data), data type, length, precision, scale, and code page.

Settings

Task Factory Rest Source Settings

Option Description
Timeout Sets the amount of time (in seconds) the component should fail if there is no response from the web service.

Note:  The default value for this option is 0, meaning the connection will not timeout.
Number of Retries Indicates the number of times to retry connecting to the API before the component fails.

Note:  The default value for this option is 0, meaning the connection attempt will not be reattempted.
Cookie Container Used to identify the variable that stores a returned cookie response. The variable must be of object datatype.

Note:  If your using cookie authentication, link you'll need to setup a data flow to capture the cookie with a request in the rest source. In a subsequent dataflow, the cookie will populate into the object variable, and it can be set with the object variable selected.

Headers (Rest Source)

Task Factory Rest Source Headers

Headers are simply information about the type of data returned. You can add and remove headers by selecting the corresponding buttons. Once a header is added, select in its Name and Variable Name fields to configure them. Headers Values can be replaced by variables using the following syntax: 

  • < @User::NameOfVariable >
  • < User::NameOfVariable >

Pagination Settings

Pagination Mode Menu Options

Retrieve from response and set URL querystring

Task Factory Rest Source Pagination Settings

Option Description
Cursor Value Token Path The token path that returns the page number, or the place where the set of results is located (page number, url, page token, etc.)  for example:
Codeblock
{ "pagingToken":"12345", "results":{"name":"Sentry One"} }
The token path in this instance is pagingToken.
Is Cursor Token Value A Url Select this option if the cursor token value is a Url.
Cursor Query String Parameter The query string parameter is appended to each round trip (after the first) to the API Endpoint URL. Example: Creating the parameter pagingToken appends it to the end of the Url such as the following: http://webservice/endpoint?pagingToken=12345.)
Cursor Finished Value The value that the token path defined at cursor value token path that displays when paging has finished. For example, if paging token 12345 ends with a 0, you would enter 0 in this spot.

Note:  This can be left blank if the endpoint omits the results token when it's finished.
Pause Between Round Trips (Seconds) The number of seconds between calls to the application.

Retrieve from response and set replacement value

Task Factory Rest Source Pagination Settings

Option Description
Response Location Select whether the response location will be Response Body or Response Header.
Page Token Path The token path for the paging token.

Example: pagingToken
Pagination Template Sets the template that can be replaced in the Endpoint Url or Post Body.

Example: ?value={%pageToken%}
Pagination Token Finished Value Identifies the final value.

Note:  This value varies by your API.
 Pause Between Round Trips (Seconds) The number of seconds between calls to the application.

Manage in component and set replacement value

Task Factory Rest Source Pagination Settings

Option Description
Page Start Variable The record number where you want to start.

Note:  Generally, this value is 1.
Increment By The number of items per page.
Max Number Of Pages The total number of pages.

Note:  This value isn’t dynamic, so can’t be entered as a value.
Pagination Template The value appended to the call and stored in the {%paging%} variable which is added to the Endpoint.
Last Page Detection Identifies the method the endpoint uses :
  • End paging when status code is  
  • End paging when response contains error message
  • End paging when resultset doesn't contain any rows
Pause Between Round Trips (Seconds) The number of seconds between calls to the application.

Authentication Results (Rest Source)

Task Factory Rest Source Authentication Results

If you need  a bearer token sent every call to the Rest API, you'll set the bearer token up here. Create a variable to dynamically set the authentication response token by selecting the Add New Mapping button.  

Additional Information: For more information about setting up a bearer token, see the following support article.

Test API (Rest Source)

Task Factory Rest Source Test Api

Once properly configured, you can select the Preview Data button to see an output sample as well as return raw XML/JSON should you need additional information to help configure the component further.

Endpoints (Rest Source)

Task Factory Rest Source Endpoints

Option Description
Choose Configuration The endpoints that are selectable for this section are configured in your config file.

Note:  The Google analytics config file provides a good example of these parameters. You can access this config file at the following file path: C:\%Program Files (x86)%\SentryOne\Task Factory\OAuth2ConfigFiles

Additional Information: For a tutorial explaining configuration files for the Rest OAuth2 connection manager, see the following video.

HTTP Error Output

The rest source can capture error responses with the Http Error Output. You are asked to choose between the Rest Source Output and Http Error Output when connecting downstream components. 

Note:  An execution error causes a package to fail unless the Http Error Output is attached.

Task Factory Data Flow Task

Error Response Path Property

The rest source depends on failure HTTP status codes. Codes such as 401, 404, and 500 are used to detect if an error has occurred during execution. If an API doesn't return failure HTTP status codes when an error occurs, you can tell the component what to look for. This allows the component to fail with an error. 

For example: a user makes a call to an API and it returns a valid status code of 200. If the JSON response is  { "response" : { "error":"Invalid auth token" } }, you can set the Error Response Path to response.error. The component detects the error response and throws an exception that can be handled. At this point, the component throws an exception and does not send the error to an error output.

This only applies to endpoints that return an HTTP status code of 200 through 208, instead of using a failure code such as 401 or 404. 

Task Factory Rest Source Data Flow Component Error Response Path

Configuring Rest Source for Visual Studio Team System (VSTS)

Configure Rest Source for Visual Studio Team System by completing the following steps:

  1. Before configuring the Rest Source, you need to set up alternative credentials in VSTS. This can be done by accessing the Security Settings for the account. Set up alternative credential in Visual Studio Team System
  2. Select Alternate authentication credentials, and then set up the username and password: Alternate authentication credentials
    Alternatively, the personal access tokens (highlighted above) can be used as the password in the REST Source.
  3. Open the Rest Source component.
  4. Create a new Basic Authentication connection manager
    Task Factory Rest Source Create New Rest Connection
  5. Enter the username and password in the connection manager. 
    Task Factory Rest Connection Manager
  6. Close the connection manager and configure the Endpoint Url. Please consult VSTS's API documentation for endpoint information. Task Factory Rest Source Endpoint URL

REST Destination

Destination Icon Destination Description
Task Factory Rest Destination Icon REST Destination enables you to connect to a web service that utilizes a REST API and send data to it. See the Rest Source Connection Manager, Rest Source OAuth Connection Manager, and the Rest Source OAuth2 Connection Manager to learn more about configuring the component's connection manager.

API Endpoint

Task Factory Rest Destination Api Endpoint

Option Description
Endpoint URL You set the URL of the web service and choose the method making the call:
  • Post - (Default) Request that the destination URI perform an action with the provided data.
  • Get - Retrieves data from the destination
  • Put - Stores data at a URI and can be used to create a new entity or update an existing one.
  • Delete - Requests that data be removed
  • Patch - Requests an update to the specified fields of an entity at a URI.

Important:  The Endpoint URL and Post Data uses auto-complete for source columns and variables. Source columns are surrounded in curly braces {{ColumnName}}. Variables use the following format: <@User::MyVariable>.

Post Data The user defined data used in the post request.
Timeout Sets the number of seconds the component should fail if there is no response from the web service.
Number of Retries Indicates the number of times to retry connecting to the API before the component fails.
Cookie Container Used to identify the variable that stores a returned cookie response. The variable must be of object datatype.

Headers (Rest Destination)

Task Factory Rest Destination Headers

Headers are information about the type of data returned. You can add and remove headers by selecting the corresponding buttons. Select the header's Name and Variable Name fields to configure them after they are added. 

Note:  Source columns and variables can be used as header values. Columns are surrounded in curly braces {{ColumnName}}. Variables use the following format: <@User::MyVariable>.

Valid Status Codes

Task Factory Rest Destination Valid Status Codes

Option Description
Valid Status Codes You can add the code to report when a valid request is made. When several status codes are used, they should be separated with the ("pipe") character.

Note:  In most cases, any 200 statuses are valid.
Error Handling
  • Not Used - Error handling is not used and no error constraint is available.
  • Ignore Failure - All errors are ignored and the package continues to execute.
  • Redirect Row - All errors are directed to an error output.
  • Fail Component - (Default) On error, the component fails execution and stops the package.

Test Endpoint (Rest Destination)

Task Factory Rest Destination Api Endpoint

Option Description
Test Endpoint button The Test Endpoint button produces a sample output based on the user's configuration.  Values can be substituted by entering them in the table above the Test Endpoint button.

Note:  This is a real call to the API with the data provided. Input Column Names cannot be changed.
Request tab The Results tab displays the data returned in the request. The Request tab displays the actual request made to the endpoint.

Endpoints (Rest Destination)

Task Factory Rest Destination Endpoints

Option Description
Choose Configuration This view displays the configuration name and endpoint value. This is generated from the configuration file chosen in an OAuth2 connection manager. Parameters are configured by selecting the endpoint and selecting the Choose Configuration button. (This opens a parameter window.)  

Sending Files to a Rest Destination

You can send files with the Rest Destination. The column must use DT_BYTES or DT_IMAGE data types. Additionally, only the column that contains the file can be included in the POST body.

Rest Destination Response Output

The Rest Destination uses the successful output to capture responses from the service.

Task Factory Rest Destination Response output

Output responses are output as NTEXT data types. To view the response in a data viewer, you need to use SSIS's native Data Conversion component.

REST Task

Task Factory Icon Description
Task Factory Rest Task Icon REST Task allows you to execute calls to a REST endpoint. See the Rest Source Basic Connection Manager, Rest Source OAuth Connection Manager, and the Rest Source OAuth2 Connection Manager to learn more about configuring the component's connection manager.

Request Settings Tab

Task Factory Rest Task Request Settings

Option Description
API Endpoint URL  In this field, you set the URL of the web service. You should also choose whether you're making a call to receive information (Get) or making a request to the service (Post.)
Get/Post Data  Specifies whether the action is a get or post request.

Note:  See your application's API page to determine the appropriate action.
Timeout  Sets the amount of time (in seconds) the component should fail if there is no response from the web service.
Number of Retries  Indicates the number of times to retry connecting to the API before the component fails.
Headers Headers are simply information about the type of data returned.
Add New Header button / Remove Header button You can add and remove headers by selecting the corresponding buttons. Once a header is added, select in its corresponding Name and Value/Variable Name fields to configure.

Note:  Header Values can be replaced by variables using the following syntax: User::NameOfVariable.

Response Handling Tab

Task Factory Rest Task Response Handling

Option Description
Save Response To  Identifies the variable that populates with the response value.
Choose File Select the file where you want to save the response.

Note:  This option enables when File is selected for the Save Response To option.
Save Content as Binary Select this option to save the content as a binary.

Note:  This option enables when File is selected for the Save Response To option.
Filter Response  Select this option to filter responses.
Response Type Select the response type you are filtering, Response Contents or Response Headers.

Note:  This option enables when Filter Response is selected.
Response Format  Select JSON, XML, or String.

Note:  This option enables when Filter Response is selected.
Response Filter  Add the words separated by commas to return only the items listed here.
Save Http Response Code  Select this option if an HTTP response code is returned.
Choose Variable  Identifies the variable that populates with the HTTP response code.

Note:  This option enables when Save Http Response Code is selected.

Error Handling (Rest Task)

Task Factory Rest Task Error Handling

Option Description
Valid Status Code  Add the valid HTTP status codes returned by the REST API. Multiple codes can be added by separating them with the pipe ( ) character.

Note:  Most of the time, 200 is the only valid status code.
Turn On Debug Mode  Package executes in debug mode.

Preview / Test

 Task Factory Rest Task Preview / Test

Once properly configured, select the Preview Data to view an output sample. The preview also returns raw XML/JSON/string data that can assist in configuring the component further.

REST Examples

Connecting to Google Analytics Example

The following is an example for configuring a Rest OAuth2 Connection Manager, and Rest Source to your Google Analytics API.

API Info

  1. Log into your Google developer account tied to analytics, and create an App or agent that communicates with Google API.
  2. Enable the Google Analytics Reporting API by going to library, and selecting Google Analytics Reporting Api. Select enable to activate the API.
  3. Create an OAuth2 Client ID by selecting Credentials > Create Credentials > OAuth Client ID. Select the application type, in this example we are using a Web Application. Provide a name for the credentials, and then enter a redirect URI. Select Create to finalize the creation of the OAuth Client ID. The entered URI needs to be an HTTP or HTTPS URL that will respond whenever the API reaches out to it. 
  4. Return to Credentials to display the newly created Client ID under the OAuth2 Client IDs section. Select the client name to obtain the Client ID, Client Secret, and Redirect URI from the API. The Client ID, Client Secret, and Redirect URI are essential to establishing the Rest OAuth2 Connection Manager, and Rest Source.

Connection Manager

  1. Create a new, or open an existing Visual Studio SSIS project and then create a new Rest OAuth2 Connection Manager. Google Analytics uses a Rest OAuth2 Connection Manager.
  2. In the Rest OAuth2 Connection Manager window, select the Google Analytics Configuration file to open the Token Getter Window.
  3. Enter the Redirect URI, the Client ID, and the Client Secret into the appropriate fields in the Token Getter. Select Get Access to establish access. Select Allow Access to the API to continue. If you are connecting to the API for the first time, you may need to log into the API to proceed.
Note:  Providing the above information into the Token Getter auto-populates many fields within the Connection Manager, including the Refresh Token tab.

Rest Source

  1. Consult the applicable API reference guide for information about Endpoints.
  2. Create a new Rest Source. Right click the Rest Source and select edit to configure the Rest Source.
  3. Connect to the OAuth2 Connection Manager by selecting the Connection Manager from the Connection Manager drop-down list. Some fields within the Rest Source auto-populate based on the information provided to the connection manger in the configuration file.
  4. In the Api Endpoint tab, enter the applicable Endpoint URL into the appropriate field, and then select the action you want the rest source to take.
  5. Select the Test API tab to test the data that the API returns. Select Preview Data to display a Raw Response from the API.
  6. Select the JSON Properties tab to configure the Root JSON path. Enter the applicable Root JSON Path, and then select OK to complete the Rest Source.
Note:  For more information about the processes involved with this example, see the video tutorials below.

REST Samples

The following REST samples are available in the taskfactory-samples repository on GitHub:

  • 1_SetupLocalRestDirectory.dtsx - Use to set up the REST Samples
  • RestTask_SaveResponseToVariable.dtsx - REST Task configured to save response to a variable
  • RestTask_FilteredResponse.dtsx - REST Task configured to filter the response of a request and save to a variable
  • RestSource_BasicPullData.dtsx.dtsx - REST Source configured to pull data from an endpoint
  • RestSource_CustomHeaders.dtsx - REST Source configured to use custom headers
  • RestSource_DotNotation.dtsx - REST Source configured to use dot notation in a JSON token
  • RestSource_VariableReplacements.dtsx - REST Source configured to use variable replacements in the Endpoint URL, PostBody, and Headers
  • RestDestination_BasicPostData.dtsx - REST Destination configured to post data to an endpoint

Tutorials

REST Tutorials

How to Connect to Yelp using the Task Factory REST Source

Tutorial walks through using the Task Factory REST Source to connect to Yelp to pull down data pertaining to local businesses.

How to make a Multipart call in the Rest Task

Some APIs (such as the public API Census GeoCoder from the United States Census Bureau), require you to make Multipart form-data calls. This example shows how to do that with the REST Task.

How to use the Unpack Data Transform with JSON

If you've connected to a REST Source, but are unable to parse more than one level of the JSON, this tutorial will help you through that.

How to use the Unpack Data Transform with XML

If you've connected to a REST Source, but are unable to parse more than one level of the XML, this tutorial will help you through that.

Using the Rest Task to get an Access Token and use it as a Bearer Token

Using the Rest Connection Manager to get an access token to use as a Bearer Token

Many APIs will need you to get an Access Token every call and use it as a Bearer Token. These examples shows you how to do that using the REST Task. 

Video Tutorials

Using Rest Source with Salesforce

Using Rest Source to Connect to Google Analytics

Rest Source Training Video Title Video

API Endpoint

Properties

Headers

Pagination Settings

Authentication Results

Test API

Rest Destination Training Video Title Video

API Endpoint

Headers

Valid Status Codes

Test Endpoint

Rest Task Training Video Title Video

Request Settings (Rest Task Video)

Response Handling (Rest Task Video)

Error Handling (Rest Task Video)