Get data from SurveyMonkey in SSIS using 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.

Here is what you have to do to consume SurveyMonkey rest api. This post assumes you already have atleast one survey with sample question.

  1. Create OAuth App -> Visit  https://developer.surveymonkey.com/apps/  (Redirect URL can be http://localhost and App name can be anything e.g. TestApp)
  2. Once App created you can copy API Key (This goes with each rest api URL)
  3. Now go to API Console https://developer.surveymonkey.com/tools/api_console/
  4. Select your App from Application dropdown
  5. Click generate Token It will ask you to Authorize..Click Yes
  6. Screen will redirect back to API Console. If doesn’t try same thing again.. It took us 2 tries to get Auth Token. Once redirect is done you will see long encoded text in Access Token (This goes in Request Header (e.g. replace xxxxx in this string “bearer xxxxxxxx” with new token)
  7. To test simple API click on get_survey_lis (This doesn’t have any required parameter). Once you click “Try it” you will get response back with list of survey and survey_id for it (e.g. “survey_id”: “73580357”).
  8. Now we ready to use JSON REST API Connector as below… (JSON Path has browse option you can try that to you don’t have to type JSON path)
Set JSON Path Filter for SurveyMonkey REST API Request

Set JSON Path Filter for SurveyMonkey REST API Request

 

Get SurveyMonkey data in SSIS using JSON REST API Connector

Get SurveyMonkey data in SSIS using JSON REST API Connector

Extract SurveyMonkey data in SSIS using JSON REST API Connector

Extract SurveyMonkey data in SSIS using JSON REST API Connector

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 REST API Pagination (Loop through large response)

SurveyMonkey REST API Pagination (Loop through large response)

SurveyMonkey API Limit

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

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.

Posted in SSIS JSON Source and tagged , , , .