{"id":279,"date":"2016-01-07T05:43:24","date_gmt":"2016-01-07T05:43:24","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=279"},"modified":"2025-04-07T13:00:11","modified_gmt":"2025-04-07T13:00:11","slug":"get-data-from-surveymonkey-in-ssis-using-rest-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-data-from-surveymonkey-in-ssis-using-rest-api\/","title":{"rendered":"How to get data from SurveyMonkey in SSIS with REST API"},"content":{"rendered":"

Introduction<\/h2>\n

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

In the nutshell we will see how to make SurveyMonkey API call and get data from there.<\/p>\n

<\/h2>\n

How to get SurveyMonkey Access token<\/h2>\n
    \n
  1. Visit \u00a0https:\/\/developer.surveymonkey.com\/apps\/<\/a>\u00a0 and Click on Add a New App<\/li>\n
  2. Gave the desire name and select the type Public or Private and Click OK.<\/li>\n
  3. It will Generate the app and redirect you on Overview there you can find ClientID and Secret.<\/li>\n
  4. Now click on Settings Second Tab and enter below URL as OAuth Redirect URL<\/li>\n
  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.<\/li>\n
  6. At the bottom of the page you can find Scopes Section Make them Required and update it.<\/li>\n
  7. At the upper right side you can also find Test OAuth Flow to Test it.<\/li>\n<\/ol>\n

    Step-By-Step : Read SurveyMonkey data in SSIS<\/h2>\n

    Here is what you have to do to consume SurveyMonkey rest api.<\/p>\n

      \n
    1. First\u00a0of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.\n
    2. From the SSIS toolbox drag and drop JSON Source on the data flow designer surface.\n
    3. Double click JSON Source and enter the following URL as below to get all Surveys details and create new HTTP Connection.\n
      https:\/\/api.surveymonkey.com\/v3\/surveys<\/pre>\n
      \"JSON<\/a>
      JSON Source set URL and Create HTTP Connection<\/p><\/div><\/li>\n
    4. Now in the enter the Web URL and Select Static Token and pass that saved SurveyMonkey Access Key in the as Token\/Key.\n
    5. Now click on Select Filter button and select the desire filter.\n
    6. That’s it, de-select the Include Parent and click on Preview button to view the Survey data.\n
    7. That’s it we are ready to get Survey details in next steps.<\/li>\n<\/ol>\n
      Let’s Get all the questions and answers of Survey by SurveyId<\/h3>\n
        \n
      1. Let’s, Get Survey details using survey id using below url. You need to replace placeholder with your survey id.\n
        https:\/\/api.surveymonkey.com\/v3\/surveys\/{survey_id}\/details<\/pre>\n
        And set below filter to get questions and answers.<\/p>\n
        $.pages[*].questions[*].answers.choices[*]<\/pre>\n
        \"JSON<\/a>
        JSON Source Select Filter<\/p><\/div><\/li>\n
      2. Furthermore, Select Include parent and Include Parent column with Array Type and set value as\u00a0 [-1:]<\/span> as last array is heading Question Text, as we want question with answers.\n
      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.\n
      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.<\/li>\n<\/ol>\n
        SurveyMonkey REST API Pagination – Loop through large response<\/h2>\n
        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)<\/p>\n
        For more information about other REST API paging methods check this link<\/a><\/p>\n
        \""<yoastmark\"<\/a><\/p>\n
        SurveyMonkey API Limit<\/h2>\n
        Survey Monkey limit number of api requests you can do in 1 second. Refer this link<\/a> to check their latest rate limit for v3 API.<\/p>\n
        You can easily implement this limit under throttling tab of JSON Source (This setting is only useful if you doing paging)<\/p>\n
        \"Limit<\/a>
        Limit REST API Requests per Seconds (API Request Throttling)<\/p><\/div>\n
        Create\/Modify Survey Automatically using REST API<\/h2>\n
        So far we talked how to consume SurveyMonkey data via REST API. But SurveyMonkey also allows to create\/edit\/delete survey\/questions\/answers\u00a0via REST API Calls. If you have that need then you can use REST API Task rather than JSON Source. SSIS REST API Task<\/span><\/a> supports sending POST request and getting response back into variable. You can also perform validation and error handling.<\/p>\n
        Loading SurveyMonkey data into SQL Server \/ Other Target<\/h2>\n
        ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
        Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
          \r\n
        1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
        2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
          \r\n\r\n\"\"<\/a>\r\n
          SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
          Step 2: Configure Target Connection<\/h3>\r\n
            \r\n
          1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
          2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n