Read Salesforce Marketing Cloud data in SSIS (ExactTarget API)

Introduction

In this post we will learn how to call Salesforce Marketing Cloud API using SSIS and load into SQL Server. We will show you use case of SSIS REST API Task  and SSIS JSON Source

Basically there are two steps to call Salesforce Marketing Cloud API

  1. Obtain ClientID and ClientSecret ( Check these steps )
  2. Get Access Token by calling requestToken API call
  3. Once you have token you can call any other API endpoints to read / edit / update Marketing Cloud data. You must supply Token inside Authorization Header along with each call.
NOTE: Salesforce Marketing Cloud is formally known as ExactTarget

Requirements

Before we look into Step-By-Step section to call Salesforce Marketing API (Exacttarget) make sure you met the following requirements.

  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. ZappySys SSIS PowerPack installed. Click on the link to download a FREE trial.
  4. You have access to Salesforce Marketing Cloud and you have correct permission to create access key (or just ask Salesforce Admin to generate it for you Check these steps )

Getting Started

Now let’s look at step by step approach on how to read salesforce marketing data in SSIS.

Step-1 : Get Access Token for Salesforce Marketing Cloud API call

First step is to get token for Marketing Cloud API calls. We have to call this API to obtain token. This token typically lasts for one hour or so (3600 seconds).

Before we call below steps make sure you have obtained ClientID and ClientSecret ( Check these steps ).

  1. Download and install SSIS PowerPack
  2. Open SSIS Package
  3. Drag ZS REST API Task from the SSIS Toolbox on Control Flow designer.
  4. Rename task to call it Get Token
  5. Double click to edit as below.
    Production URL:
    Sandbox URL:
    Get API Token for Salesforce Marketing Cloud API calls

    Get API Token for Salesforce Marketing Cloud API calls

  6. Go to response setting tab and change Response Type to JSON and set expression as $.accessToken , Check Save response and Select Variable <New Variable> (e.g. vToken)
    Configure Response Tab - Extract API Token for Salesforce Marketing Cloud API calls

    Configure Response Tab – Extract API Token for Salesforce Marketing Cloud API calls

  7.  Now Click Test Request/Response to confirm you get token. Copy Token from Bottom panel and click OK to save UI
  8. Now right click in designer and click “Variables” and paste Token we got in previous step in the Value (This token is good for one hour only so update if it expires). This manual update is only needed while you designing page… and testing data Preview / get metadata (see Next Section).  When you run full package it will get fresh token each time and ignore Hardcoded token from Variable.

Step-2 : Read data from Salesforce Marketing Cloud API (SSIS JSON Source)

Now once we have token extract step done. We can move to next step which is to read actual data from Exacttarget API (i.e. Salesforce Marketing Cloud API)

We will us SSIS JSON Source to read data (JSON format) and save to SQL Server.

  1. Drag data flow task from SSIS Toolbox and drop to control flow surface.
  2. Rename Data flow to Get Data or Call Data
  3. Connect first step (Get Token) to data flow
  4. Double click task and from data flow toolbox drag  ZS JSON Source on the surface
  5. Double click JSON Source and configure as below.
  6. Click ON Select Filter Button and Select Correct filter… (Make sure select only Array Node icon to extract correct data)
  7. Click Preview to confirm
  8. Click OK to save
  9. Connect JSON source to some target (e.g. OLEDB Destination for SQL Server)
  10. Run your package
Read Salesforce Marketing Cloud data in SSIS and load into SQL Server (Using SSIS JSON Source)

Read Salesforce Marketing Cloud data in SSIS and load into SQL Server (Using SSIS JSON Source)

Debug Web API call using Fiddler

To test how things looking behind the scene we strongly suggest to use tool like  fiddler. You can double click the URL entry (Right side) to see Request and Response Panels. Top panel is Request (URL, Headers, Body) and Bottom Panel is Response.
Debugging Web API call using Fiddler in SSIS

Debugging Web API call using Fiddler in SSIS

Loading data from REST API to SQL Server

Once you setup HTTP / OAUTH connection we can use JSON Source or XML Source to extract data from API and  load into SQL Server. If you are API is XML based API then use XML Source in below examples else use JSON Source. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors To load REST API data into SQL Server perform the following steps (Screenshots are generic and used for demonstration for purpose, your values can be different than below)
  1. Go to SSIS Data Flow tab
  2. Drag ZS JSON Source from the SSIS Toolbox (Use ZS XML Source if your API is XML based API)
  3. Double click to edit Source
  4. Set API URL you like to call, Check Use Credentials if you need to use HTTP or OAuth connection
  5. Set Method / Body if its other than GET. For method other than GET make sure you select correct Content-Type from Dropdown (e.g. application/json). This indicate input data format in Body.
  6. Set HTTP Headers if needed (in Most cases you won't need to set this)
  7. Select Filter (many times this is not needed). If your API response has array node then select it here. For example if your response looks like this { rows: [ {...}, {...}, {...} ] } then filter would be $.rows[*]. Select Array node you like to extract in the Filter selection UI.
  8. Configure Pagination if needed on the pagination tab. Check your API documentation see it supports pagination.
  9. Click OK to Save component settings and generate desired metadata / columns for the output.
  10. Now drag OLEDB destination from the SSIS toolbox and drop on the Data Flow surface (technically you can use any valid Destination but for demo we will use OLEDB Destination)
  11. Connect Source to Destination Component
  12. On OLEDB destination select / create new SQL Connection and then Click "New Table"
  13. Click on Mappings tab to map columns by names and click OK to save
  14. Execute the Package
Configure SSIS OLEDB Destination - Loading Xero Data into SQL Server Table

Configure SSIS OLEDB Destination – Loading REST API Data into SQL Server Table

Xero to SQL Server Column Mappings for OLEDB Destination

REST API to SQL Server Column Mappings for OLEDB Destination

Loading Xero data to SQL Server in SSIS

Loading REST API to SQL Server in SSIS

POST / Write Data to API (Insert, Update or Delete)

There will be a time you like to automate POST actions (e.g. create new incident via API call). Check this article to learn more on how to POST , DELETE, PUT data using API calls

Common Errors

There are many error can occur during runtime. Here are most common errors you may face at runtime.

Truncation related error

The most common error you may face when you run a SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is authentication error, which happens when you deploy/copy a package to another machine and run it there. Check Deployment to Production below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords, when you run it from another machine using another Windows account. To circumvent this when you are creating a SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way you won’t face authentication related errors when package is deployed to a production server. Check our article on how to configure packages with sensitive data on your production or development server.
Posted in REST API Integration, SSIS JSON Source (File/REST), SSIS REST API Task and tagged , , , , .