How to read Salesforce Marketing Cloud data in SSIS using REST API Call

In this post, we will learn how to call Salesforce Marketing Cloud API using SSIS. 

NOTE: Salesforce Marketing Cloud is formally known as ExactTarget

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 a 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.

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

The 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). 

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

   Sandbox URL:
   

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

   
   
   
   
   
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)
   
   
   
   
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 

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/