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

OptionDescription
Configuration FileThe 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 AuthenticationOption for endpoints that do not require authentication.

Note:  Open the component properties to configure this option.
User NameThe username needed to authenticate to the endpoint URL.
PasswordThe password that allows you to authenticate to the endpoint URL.
Use Base 64 Encoded Authentication HeaderSelect 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 ProtocolThe 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.

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.

OptionDescription
Rest Authentication Endpoint UrlThe 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 InThe Rest Source allows you to connect to endpoints with JSON and XML formats. This selection determines which format the component uses.
Successful Authentication TokenThe 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

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

Proxy

Task Factory Rest Connection Manager Proxy tab

OptionDescription
Proxy HostWhen connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy PortThe port number that corresponds to the URL proxy host.
User NameThe username needed to authenticate to the proxy.
PasswordThe password that allows you to authenticate to the proxy.

Advanced Options

Task Factory Rest Connection Manager Advanced Options

OptionDescription
Ignore Ssl Certificate VerificationSelecting 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 RedirectsSelecting 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

OptionDescription
Configuration FileThe 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 SecretAuthentication 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 TypeIdentifies the hash algorithm used in the vendor's authentication process.
Realm and VerifierOptional properties supplied by the vendor that the connection is authenticating with.
Security ProtocolThe 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

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

Proxy

Task Factory OAuth Connection Manager Proxy

OptionDescription
Proxy HostWhen connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy PortThe port number that corresponds to the URL proxy host.
User NameThe username needed to authenticate to the proxy.
PasswordThe password that allows you to authenticate to the proxy.

Advanced Options

Task Factory Rest OAuth Connection Manager Advanced Options

OptionDescription
Ignore Ssl Certificate VerificationSelecting 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 RedirectsSelecting 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

OptionDescription
Configuration FileYou 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 SecretAuthentication information provided to users at the application's developer site.
Access TokenTo 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 TokenSelect this option if the Access Token is a bearer token.
Access Token Expiration DateThe expiration date of the access token.

Note:  This settings is optional.
Security ProtocolThe 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.

OptionDescription
Choose Settings FileYou 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 KeyAuthentication information provided to you at the application's developer site.

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

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

Example: https://api.citrixonline.com/oauth/authorize?client_id={ApiKey})
Grant / Response Code ParameterThe parameter name used to store the value within the Access Token Request Url Query String.
 Access Token Request UrlThe Url used to request an access token from the service.

Example: https://api.citrixonline.com/oauth/access_token
Get / Post SelectboxDetermines whether the call is a Get or Post action.
Access Token Request Url Query StringThe query that is appended to the end of the Access Token Request URL. This query contains all of the required parameters needed to be passed to the service to retrieve the access token.
Get TokenSelecting 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
Task Factory Rest OAuth2 Connection Manager Refresh Token Xml

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.

OptionDescription
Refresh TokenAuthentication information provided to you at the application's developer site.

Note:  This should match the API Key from the Connection Settings window.
Token Request UrlThe URL that returns a refresh token. Example: https://api.citrixonline.com/oauth/access_token
HeadersAllows 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 InSpecifies how results are returned from the application (JSON, XML, or String.)
Access Token PathThe token path for the access token. Example: access_token
Refresh Token PathThe token path for the refresh token. Example: refresh_token
Use Token StoreThis option creates a file on your machine that saves the access token, and refresh token in an encrypted format.
Token Store IdIdentifies the Token Store Id.
Token Store Path (Optional)Identifies the Token Store path.
Test Get Refresh TokenSelect 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

OptionDescription
Refresh TokenAuthentication information provided to you at the application's developer site.

Note:  This should match the API Key from the Connection Settings window.
Token Request UrlThe URL that returns a refresh token. Example: https://api.citrixonline.com/oauth/access_token
HeadersAllows 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 InSpecifies how results are returned from the application (JSON or XML.)
Access Token PathThe token path for the access token. Example: access_token
Refresh Token PathThe token path for the refresh token. Example: refresh_token
Group IndexIdentifies the index position to return (0 based).
Use Token StoreThis option creates a file on your machine that saves the access token, and refresh token in an encrypted format.
Token Store IdIdentifies the Token Store Id.
Token Store Path (Optional)Identifies the Token Store path.
Test Get Refresh TokenSelect this option to test the retrieval of the Refresh Token.

Proxy

Task Factory Rest OAuth2 Connection Manager Proxy tab

OptionDescription
Proxy HostWhen connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy PortThe port number that corresponds to the URL proxy host.
User NameThe username needed to authenticate to the proxy.
PasswordThe password that allows you to authenticate to the proxy.

Advanced Options

OptionDescription
Ignore Ssl Certificate VerificationSelecting 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 RedirectsSelecting 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. Task Factory Rest OAuth2 Connection Manager select ellipsis
  2. Enter the RedirectUri, ClientId, and Client Secret configured within your Rest application. Select Get Access Token, to expand the window, and enter the application's credentials.  Note:  You may be prompted to allow or deny access to the application. 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 IconSource Description
Task Factory Rest Source IconThe 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

OptionDescription
API Endpoint URLIn 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  DataYou 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.
TimeoutSets 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 RetriesIndicates 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.
Results Returned InSelects the format for the results:  XML or JSON.
Cookie ContainerUsed 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.

JSON, String, and XML Properties

JSON Properties Configuration 

Task Factory Rest Source Json Properties

OptionDescription
Root JSON PathJSON queries can return multiple levels, therefore, this field Identifies the root to be used.
Output ColumnsIn 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.

OptionDescription
Root XML QueryXML and JSON queries can return multiple levels, therefore, this field Identifies the root to be used.
Output ColumnsIn 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.

OptionDescription
RegEx Parse PatternThe 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 ColumnsIn 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.

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

OptionDescription
Cursor Value Token PathThe 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 UrlSelect this option if the cursor token value is a Url.
Cursor Query String ParameterThe 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 ValueThe 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

OptionDescription
Page Token PathThe token path for the paging token.

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

Example: ?value={%pageToken%}
Pagination Token Finished ValueIdentifies 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

OptionDescription
Page Start VariableThe record number where you want to start.

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

Note:  This value isn’t dynamic, so can’t be entered as a value.
Pagination TemplateThe value appended to the call and stored in the {%paging%} variable which is added to the Endpoint.
Last Page DetectionIdentifies 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 ResultsIf 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

OptionDescription
Choose ConfigurationThe 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
Rest Source Error Response Path in properties

Task Factory Rest Source Error Response Path
Rest Source Error Response Path in component

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. Task Factory set up alternative credential in Visual Studio Team System
  2. Select Alternate authentication credentials, and then set up the username and password: Task Factory Alternate authentication credentialsAlternatively, 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 managerTask 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 IconDestination Description
Task Factory Rest Destination IconREST 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

OptionDescription
Endpoint URLYou 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 DataThe user defined data used in the post request.
TimeoutSets the number of seconds the component should fail if there is no response from the web service.
Number of RetriesIndicates the number of times to retry connecting to the API before the component fails.
Cookie ContainerUsed 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

OptionDescription
Valid Status CodesYou 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

OptionDescription
Test Endpoint buttonThe 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 tabThe 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

OptionDescription
Choose ConfigurationThis 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 IconDescription
Task Factory Rest Task IconREST 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

OptionDescription
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.
HeadersHeaders are simply information about the type of data returned.
Add New Header button / Remove Header buttonYou 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

OptionDescription
Save Response To Identifies the variable that populates with the response value.
Filter Response Select this option to filter responses.
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

OptionDescription
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 TitleVideo

API Endpoint


Properties


Headers


Pagination Settings


Authentication Results


Test API


Endpoints

Coming soon.
Rest Destination Training Video TitleVideo

API Endpoint


Headers


Valid Status Codes


Test Endpoint


Endpoints

Coming soon.
Rest Task Training Video TitleVideo

Request Settings (Rest Task Video)


Response Handling (Rest Task Video)


Error Handling (Rest Task Video)


Preview (Rest Task Video)

Coming soon.