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.

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:
   
   https://auth.exacttargetapis.com/v1/requestToken

   1	https://auth.exacttargetapis.com/v1/requestToken

   Sandbox URL:
   
   https://auth-test.exacttargetapis.com/v1/requestToken

   1	https://auth-test.exacttargetapis.com/v1/requestToken

   

   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

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)

Using HTTP Connection (New version – Single Step)

If you are using a new version (with HTTP – Dynamic Token Support), then you can try the following option.

1. On JSON Source Check Use Credentials and choose HTTP connection.
   

   Read Salesforce Martketing Cloud Data using REST API (in SSIS)

2. On the HTTP Connection, choose the following settings
   1. For URL enter https://auth.exacttargetapis.com/hub/v1/campaigns/
   2. Choose Dynamic Token Auth
   3. Enter Client ID (same as the previous section)
   4. Enter Client Secret  (same as the previous section)
   5. Click the Configure Dynamic Token link
      

      Salesforce Marketing Cloud API Call in SSIS Using HTTP Connection (Dynamic Token Method)

3.  Now on the Dynamic Token Tab configure like below (Request Settings Tab)
   1. Make sure you are on Request Settings Tab
   2. Enter token endpoint URL
      
      https://auth.exacttargetapis.com/v1/requestToken

      1	https://auth.exacttargetapis.com/v1/requestToken

   3. Set Request Method (POST)
   4. Select Content Type (application/json)
   5. Enter Body like below
      
      JavaScript

      { clientId: "[$userid$]",clientSecret: "<<[$password$],FUN_JSONENC>>" }

      1	{ clientId: "[$userid$]",clientSecret: "<<[$password$],FUN_JSONENC>>" }

   6. Click on Response Settings Tab
      

      Salesforce Marketing Cloud API Call in SSIS Using HTTP Connection (Dynamic Token – Request Settings)

4. On the Response Settings Tab configure like below
   1. Select Extract From Body
   2. Expression Type as Json
   3. Enter expression <span class="lang:default decode:true crayon-inline ">$.accessToken
      

      Salesforce Marketing Cloud API Call in SSIS Using HTTP Connection (Dynamic Token – Response Settings)

5. Click OK and you can preview your API data on JSON Source
   Example: https://auth.exacttargetapis.com/hub/v1/campaigns/
6. If you use HTTP COnnection with Dynamic Token you do not need two step approach we did in the previous section

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

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 REST API Data into SQL Server Table

REST API to SQL Server Column Mappings for OLEDB Destination

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.