- 1 Introduction
- 2 How to get SurveyMonkey Access token
- 3 Step-By-Step : Read SurveyMonkey data in SSIS
- 4 SurveyMonkey REST API Pagination – Loop through large response
- 5 SurveyMonkey API Limit
- 6 Create/Modify Survey Automatically using REST API
- 7 Loading SurveyMonkey data into SQL Server / Other Target
- 8 Things have gone bad: Error handling & debugging
- 9 Conclusion
- 10 References
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
- Visit https://developer.surveymonkey.com/apps/ and Click on Add a New App
- Gave the desire name and select the type Public or Private and Click OK.
- It will Generate the app and redirect you on Overview there you can find ClientID and Secret.
- Now click on Settings Second Tab and enter below URL as OAuth Redirect URL
- On that tab you can find Access Token, copy and store it some where for future use in our component to make API call.
- At the bottom of the page you can find Scopes Section Make them Required and update it.
- 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.
- First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
- From the SSIS toolbox drag and drop JSON Source on the data flow designer surface.
- Double click JSON Source and enter the following URL as below to get all Surveys details and create new HTTP Connection.
- Now in the enter the Web URL and Select Static Token and pass that saved SurveyMonkey Access Key in the as Token/Key.
- Now click on Select Filter button and select the desire filter.
- That’s it, de-select the Include Parent and click on Preview button to view the Survey data.
- 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
- 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.1$.pages[*].questions[*].answers.choices[*]
- 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.
- 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.
- 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)
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
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
- Click OK to Save Upsert Destination Settings
- 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
- To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
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)".
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:
- 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.
- 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:
- 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:
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):
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.
Finally, you can use the following links for more information: