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

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

   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.
   
   https://api.surveymonkey.com/v3/surveys

   1	https://api.surveymonkey.com/v3/surveys

   

   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

5. Now click on Select Filter button and select the desire 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

    

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.
   https://api.surveymonkey.com/v3/surveys/{survey_id}/details

   1	https://api.surveymonkey.com/v3/surveys/{survey_id}/details

   And set below filter to get questions and answers.

   $.pages[*].questions[*].answers.choices[*]

   1	$.pages[*].questions[*].answers.choices[*]

   

   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

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

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

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)

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

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:
   

   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.

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

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:

• Help File: JSON Source(REST API or File)
• SurveyMonkey API Doc