How to get data from SurveyMonkey in SSIS with REST API

Introduction

In this post we will see how to get data from SurveyMonkey in SSIS using their REST API. Interacting with REST API requires programming skill and if you are not Programmer you may face dead end. SSIS PowerPack JSON REST API Connector (for XML REST API check XML Source Connector) makes it super easy to consume any REST API data source.

In the nutshell we will see how to make SurveyMonkey API call and get data from there.

How to get SurveyMonkey Access token

  1. Visit  https://developer.surveymonkey.com/apps/  and Click on Add a New App
  2. Gave the desire name and select the type Public or Private and Click OK.
  3. It will Generate the app and redirect you on Overview there you can find ClientID and Secret.
  4. Now click on Settings Second Tab and enter below URL as OAuth Redirect URL
  5. On that tab you can find Access Token, copy and store it some where for future use in our component to make API call.
  6. At the bottom of the page you can find Scopes Section Make them Required and update it.
  7. At the upper right side you can also find Test OAuth Flow to Test it.

Step-By-Step : Read SurveyMonkey data in SSIS

Here is what you have to do to consume SurveyMonkey rest api.

  1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

    Drag and Drop : SSIS Data Flow Task from SSIS Toolbox

  2. From the SSIS toolbox drag and drop JSON Source on the data flow designer surface.
    Drag and Drop JSON Source Component

    Drag and Drop JSON Source Component

  3. Double click JSON Source and enter the following URL as below to get all Surveys details and create new HTTP Connection.
    JSON Source set URL and Create HTTP Connection

    JSON Source set URL and Create HTTP Connection

  4. Now in the enter the Web URL and Select Static Token and pass that saved SurveyMonkey Access Key in the as Token/Key.
    HTTP Connection : Static Token

    HTTP Connection : Static Token

  5. Now click on Select Filter button and select the desire filter.
    JSON Source Select Filter

    JSON Source Select Filter

  6. That’s it, de-select the Include Parent and click on Preview button to view the Survey data.
    JSON Source Preview

    JSON Source Preview

     

  7. That’s it we are ready to get Survey details in next steps.

Let’s Get all the questions and answers of Survey by SurveyId

  1. Let’s, Get Survey details using survey id using below url. You need to replace placeholder with your survey id.

    And set below filter to get questions and answers.

    JSON Source Select Filter

    JSON Source Select Filter

  2. Furthermore, Select Include parent and Include Parent column with Array Type and set value as  [-1:] as last array is heading Question Text, as we want question with answers.
    JSON Source Include Parent Columns with Array Type

    JSON Source Include Parent Columns with Array Type

  3. Now click on Preview button and you will see id, text and descriptions of Question and it’s answers along with some other fields Scores and page-id and so on.
    JSON Source Preview

    JSON Source Preview

  4. That’s it we are ready to load this data in the SQL server but before that we will see some others configurations like pagination and so on as below.

SurveyMonkey REST API Pagination – Loop through large response

By default SurveyMonkey returns maximum 1000 rows in a single web response. If you have more than 1000 records then you have to adjust paging (see below screenshot). You have to define page=1 and per_page=1000 or less in the url. In below Paging End Strategy we keep fetching data until last page is reached (If no more data found under $.data[*] node)

For more information about other REST API paging methods check this link

"<yoastmark

SurveyMonkey API Limit

Survey Monkey limit number of api requests you can do in 1 second. Refer this link to check their latest rate limit for v3 API.

You can easily implement this limit under throttling tab of JSON Source (This setting is only useful if you doing paging)

Limit REST API Requests per Seconds (API Request Throttling)

Limit REST API Requests per Seconds (API Request Throttling)

Create/Modify Survey Automatically using REST API

So far we talked how to consume SurveyMonkey data via REST API. But SurveyMonkey also allows to create/edit/delete survey/questions/answers via REST API Calls. If you have that need then you can use REST API Task rather than JSON Source Connector. SSIS REST API Task supports sending POST request and getting response back into variable. You can also perform validation and error handling.

Loading SurveyMonkey data into SQL Server / Other Target

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
  2. Connect our Source component to Upsert Destination
  3. Double click Upsert Destination to configure it
  4. Select Target Connection or click NEW to create new connectionConfigure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings SSIS Upsert Destination - Columns Mappings
  7. Click OK to Save Upsert Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

Truncation related error

The most common error you may face when you run an 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 on 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 an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Things have gone bad: Error handling & debugging

Incidentally, bad things can happen. A remote server may go offline or your server may go out of memory. In any case, you may want to know when that happens and take actions accordingly. For that purpose, you have to redirect bad rows to some other destination. For this example, we will take and use Web API Destination, but basically, you can use any SSIS component:

Handling errors

  1. Add a Derived Column above Web API Destination with expression "(DT_WSTR,4000)ZS_JSON_OUT" and name it "JsonAsString". This will let you see what JSON you are actually passing.
  2. Then add a database or file destination or use another Trash Destination for debugging purposes and redirect the bad rows (red arrow) from Web API Destination into it. Don't forget to set Redirect row option for both, Error and Truncation columns:
    Redirect bad rows from <em>Web API Destination</em> to <em>Trash Destination</em> when load from SQL Server to Elasticsearch is failing. Add derived column JsonAsString to be able to read JSON you are using.

    Redirected failed requests from Web API Destination to a desired destination when loading from SQL Server to REST API Service is failing. Derived Column JsonAsString added to be able to read JSON which was passed to Elasticsearch

  3. Finally, add a Data Viewer for the red path, if you want to debug the flow. You will be able to see URL, JSON and the error message for each record. You may want to copy-paste ErrorMessage to Notepad if you want it to be more readable:
    Use Data Viewer to view HTTP requests that failed to be fulfilled in Elasticsearch

    Use Data Viewer to view HTTP requests that failed to be fulfilled.

NOTE: You can read more about redirecting rows in SSIS Error Handling (Redirect bad rows) article.

Debugging HTTP requests

A common thing you have to do when working with HTTP requests is to debug those requests; e.g. to check what headers, body or URL was passed. To test how things look behind the scenes we strongly suggest to use Fiddler - a popular web debugging tool.

Inside it, you can double-click the URL entry (Right side) to see Request and Response Panels. The top panel is Request (URL, Headers, Body) and Bottom Panel is Response. For https:// (secure URL) make sure you enable HTTPS option in Fiddler (Tools > Options > HTTPS > Check Decrypt https request):

Debugging Web API call using Fiddler in SSIS

Debugging Web API call using Fiddler in SSIS

Conclusion

SurveyMonkey data integration via REST API can be quite challenging without right set of tools. SSIS PowerPack gives you total control on various aspects of calling REST API without doing any programming (e.g. C#, Java, Python, Ruby). Using drag and drop familiar user interface it can speed up your development by several folds.  Download SSIS PowerPack and try it out by your self.

References

Finally, you can use the following links for more information:

 

Posted in REST API Integration, SSIS JSON Source (File/REST) and tagged , , , , .