SSIS Salesforce API Task
PreviousNext

SSIS Salesforce API Task can be used to call any Salesforce API (REST or SOAP) which can be in XML or JSON format. This task provides easy way to save response into file or variable.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial we are going to learn how to GET and POST XML or JSON format Data into Salesforce Storage using ZS Salesforce API Task (In this case its JSON format).
  1. To develop ETLs in Salesforce using SSIS you’ll need a Salesforce Developer account. If you don’t have one, don’t worry, you can create a limited version FREE developer account. Click here to Sign up for FREE developer account.

    In order to connect to Salesforce Storage from SSIS you will need User Name, Password and Security Token. Your keys will look something like this: (this is just example which may differ in your case).
         User Name : yourusername@yourcompanyname.com
    Security Token : 1Abcd2efg3uHL4bda9PTS2l7 
    
    click here to learn how to obtain Salesforce Security Token.
    
  2. Now, You need to Download and Install SSIS ZappySys PowerPack.
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Here, In Visual Studio, drag and drop the ZS Salesforce API Task in the design surface.
    SSIS Salesforce API Task - Drag and Drop
  5. Here, we need Salesforce Storage Connection.

How to create Salesforce Storage Connection.

  1. Right click in the Connection Manager panel and click New Connection.
    SSIS Salesforce API Task - Create Connection
  2. Select ZS-SALESFORCE Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS Salesforce API Task - Create Connection
  3. Now in Connection Manager UI, Enter your SalesForce Credentials (User Name, Password and Security Token), and leave all other properties as it is.
    SSIS Salesforce API Task - Create Connection
  4. Click on Test Connection. If test successful then hit OK to close the connection manager dialogbox.

How to GET Response using Salseforce API Task.

  1. Double click on ZS SalesForce API Task to configure it.
  2. While making Salesforce Request you have to configure following items in your ZS SalesForce API Task. Set HTTP Request Method to GET, You can use Variable in Direct URL to make Dynamic. Just replace your variable name.
    https://na34.salesforce.com/services/data/{{User::varVersion}}/limits
    SSIS Salesforce API Task - Call REST API Example (i.e JSON API)
  3. Click on Test Request/Response to get response from Salesforce.
  4. Click OK to save Salesforce API Task configure setting UI.

How to POST data using Salseforce API Task.

  1. Double click on ZS SalesForce API Task to configure it.
  2. Select connection, enter Direct URL for practice it. set HTTP Request Method to POST,
    https://na34.salesforce.com/services/data/v20.0/sobjects/Account/
    Pass Request Data, For more information you can check Click here and set Body Content Type to JSON(application/json).
    {"Name" : "Ray"}
    
    SSIS Salesforce API Task - POST JSON/XML
  3. Click on Test Request/Response to get response from Salesforce.
  4. Go to Response Settings Tab, Save Response into Variable. Select Response content Type Json and Write Response Content Filter Expression. Check on Save Response Content and Select Save Mode to Variable.
    SSIS Salesforce API Task - Response Settings (Save response content options)
  5. Thats it, Execute your ZS SalesForce Task.

Properties

Property Name Description
SupportConnectionLessUrl
EnableStatusCheck Enable status check loop. When this option is enabled web requests are tried every N seconds to check desired value in response. Once desired value is found response is returned. Error is returned if failure indicator found or timeout occurs (i.e. Max iteration reached and still no success value found in the response).
StatusCheckMaxWaitSeconds Maximum wait time in seconds until we need to keep checking for success value or failure value indicator.
StatusFieldFilterType Extract type for success value / failed value field. For JSON Examples: $.status.value,  For Xml Examples: ./AuthInfo/Token/text()  --or-- ./Auth/@Token --or-- //*[local-name() = 'ResponseData']

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
Json [1] Json
Xml [2] Xml
Regex [3] Regex
StatusFieldFilterExpr
StatusCheckIterationWaitSeconds Wait time between each iteration for status check
StatusCheckForFailedValue Enable check to look for failure indicator. By default only SuccessValue is searched in response unless you set this option to true. Set StatusFailedValue to indicate failure regex / string pattern.
StatusSuccessValue Regex pattern or string value you like to search for Success indicator (e.g. Done|Success|Ok ). If you dont want to use Regex match then change StatusFieldFilterType.
StatusContinueValue Regex pattern or string value you like to search to continue status check (e.g. Done|Success|Ok ) If this value is found then status check is continue without checking for success or failure value. If you dont want to use Regex match then change StatusFieldFilterType.
StatusFailedValue Regex pattern or string value you like to search for Failure indicator (e.g. Cancelled|Aborted|Failed ). Set StatusCheckForFailedValue=true to use this value. If you dont want to use Regex match then change StatusFieldFilterType.
IsMultiPartUpload Check this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value)

==== Raw Upload (Content-Type: application/octet-stream) =====  
To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g.  @c:\data\myfile.zip )

==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) =====  
To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored.
If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type.
See below Example of uploading multiple files along with additional fields.

file1=@c:\data\Myfile1.txt
file2=@c:\data\Myfile2.json
file2.Content-Type=application/json
SomeField1=aaaaaaa
SomeField1.Content-Type=text/plain
SomeField2=12345
SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab
SomeFieldStartingWithAtSign=\@MyTwitterHandle
AddMultiPartPostamble Some API server requires Multi-Part Requests to be ended by new line characters (i.e. \r\n). Enable this option if you face error such as - Unexpected end of MIME multipart stream. MIME multipart message is not complete -
PreventAutoRedirect By default HTTP 301 and 302 redirects are allowed. For some reason you want to disable it then set this option.
RequestTimeoutMs HTTP request timeout in milliseconds. Enter 0 to use system default timeout.
SecurityProtocol Specifies which security protocol is supported for HTTPS communication. Using this option you can enable legacy protocol or enforce to use latest version of security protocol (Note: TLS 1.2 is only supported in SSIS 2014 or Higher).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] System Default
Ssl3 [1] SSL v3.0
Ssl3Plus [2] SSL v3.0 or higher
Tls [3] TLS v1.0
TlsPlus [4] TLS v1.0 or higher
Tls11 [5] TLS v1.1
Tls11Plus [6] TLS v1.1 or higher
Tls12 [7] TLS v1.2
Tls12Plus [8] TLS v1.2 or higher
EnableCompressionSupport Enable support for gzip or deflate compression. When you check this option compressed response automatically de-compressed saving bandwidth. This option is only valid if web server supports compressed response stream. Check your API documentation for more information.
EnableRequestCompression Enable this option if you like to compress request body (i.e. Content-Encoding: gzip). Enabling this option can speedup data transfer especially when you are sending large amount data (i.e. Upload file)
AllowUnsecureSuite AllowUnsecureSuite
IgnoreCertificateErrors IgnoreCertificateErrors
RequestUrl Request URL where you want to submit HTTP Request
RequestUrlConnection HTTP Connection Manager for WebRequest to specified URL
RequestMethod Request method (e.g. GET, PUT, POST...)
RequestContentType Specifies content type for data you wish to POST. If you select Default option then system default content type will be used (i.e. application/x-www-form-urlencoded). If you specify Content-Type header along with this option then header value takes precedence.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
TextPlain [1] Text (text/plain)
ApplicationJson [2] JSON (application/json)
ApplicationXml [3] XML (application/xml)
TextXml [4] XML (text/xml)
TextXmlUtf8 [5] XML (text/xml;charset=UTF-8)
TextHtml [6] HTML (text/html)
ApplicationFormUrlencoded [7] Form (application/x-www-form-urlencoded)
Raw [8] Raw (No content-type)
OverrideConnectionUrl When this option is checked you can use Direct URL instead of using URL coming from selected HTTP connection manager (This option is only valid when RequestAccessMode = UrlFromConnection)
RequestHeaders Headers you want to send along with your request
RequestData Data you want to send along with request. This is not supported if Request Method is GET
SaveResponse Check this option if you want to save response to variable or file
SaveResponseStatusCode Check this option if you want to save response status code (numeric) to variable
ResponseAccessMode Determines how to save response content

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Variable [0] Save to Variable
File [1] Save to File
SaveAsBinary Treat response data as binary data rather than text data. Check this option when you downloading data which is not in text format or doesn't have encoding (e.g. zip, mp3, jpeg). This option is not available when you save response to Variable. When this option is checked ResponseCharset option is ignored.
ResponseDataFilePath File path where response content should be saved
ResponseHeaderMappings Http response header mappings
ResponseCookieMappings Cookie mapping. Use this setting to map cookie value to variable
CookieContainerVariable Cookie Container can be used to maintain state between multiple web requests. Example: You can login to site like wordpress and then extract any private page content by simply passing authentication cookies using this variable.
ConvertXmlToJson Convert XML Response to JSON. This setting is helpful to consume services which support XML format but not JSON. By converting XML to JSON you can then consume data using other components/tasks such as JSON Source, JSON Parser.
ResponseDataVariable Variable name where response content should be saved
ResponseStatusCodeVariable Variable name where you want to save response status code
ResponseContentType Response content type

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
Json [1] Json
Xml [2] Xml
Regex [3] Regex
ResponseContentFilter If you selected ResponseContentType=XML or JSON then use this property to filter Response Content using JSONPath or XPath expression.
ContinueOnResponseFilterError Continue on response filter error (i.e. Filter property referenced in expression (JSON/XML/RegX) not found in response)
ResponseCharset Specifies content encoding for response. Leave this blank to auto detect based on Content-Type header. When you specify this property then charset sent in response header is ignored (e.g. Content-Type: text/plain; charset=utf-8).
ValidateResponse If you want to validate for certain condition after response received check this option
ValidationRules Validation rules for response
ConsumeResponseOnError When error occurs no data is returned. Use this option to get content eventhough error occurs. When this option is checked you can't use [continue on error when specific string found in response] option
ContineOnAnyError Continue when any type of exception occurs during http request
ContineOnErrorForMessage Continue on error when specified substring found in response
ContineOnErrorForStatusCode Continue on error when specified status code returned from web server
ErrorStatusCodeToMatch Status code to match when error occurs and ContineOnErrorForStatusCode option is true. If Response status code matches to this code then task continues to run
MatchForEqual If true error is checked for equal condition else not equal (StatusCode and ErrorMessage)
ErrorSubstringToMatch Error substring to match when error occurs and ContineOnErrorForMessage option is true. If Response status code matches to this code then task continues to run
ContineOn404Error Continue if response code 404 (Usually returned when specified Url is not found)
SaveErrorFlagToVariable Save Boolean flag into SSIS variable if error is detected  (Only valid if error handling options checked)
ErrorFlagVariableName Variable name where you want to store error flag (Only valid if error handling options checked)
LoggingMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.

Setting UI

SSIS Salesforce API Task - Setting UI
SSIS Salesforce API Task - Setting UI

See Also

Articles / Tutorials


Copyrights reserved. ZappySys LLC.