How to Automatically pull Interview data from SurveyToGo to your SQL Server table (Creating a Read Replica Table)

Overview

When integrating SurveyToGo with 3rd party systems, Dashboard Portals or reporting tools, it is often necessary to have SQL access to the interview data in order to have these external systems access the interview data. In case you only need to access the tabulated format of the data of the interviews, please explore the Tabulation REST API which was specifically created for that purpose.

This guide explores the SurveyToGoReadReplicaCreator command line tool and explains how to use it to automatically pull recently uploaded/modified interview data of a survey and push it into your SQL server table.

 

Step 1: Using the SurveyToGoReadReplicaCreator Command Line tool

The SurveyToGoReadReplicaCreator will use the SurveyToGo REST API to pull the recently uploaded/modified interviews of a specific survey and push them into your SQL server table (named in this article the Read Replica table). You can download the SurveyToGoReadReplicaCreator command line tool from here: https://dblstg.dooblo.net/downloads/SurveyToGoReadReplicaCreator.zip 

The SurveyToGoReadReplicaCreator tool takes the following command line arguments: 

Argument Description
/surveyid=xxx The survey id of the survey you wish to get the data for
/userid=xxx The API user id to use
/password=xxx The password to use
/window=xx Fetch the interviews that were modified in the last XX hours.
/connstr=”xxxx” The connection string to the SQL Server database that is the Read Replica
/tablename=xxx The table name of the read replica. The table needs to be created ahead of time.
/vars=”xxx,yyy,ttt” Comma separated list of variable names to include in the SimpleExport and in the read replica.
[OPTIONAL] /action=ListOnly If this is specified, instead of actually running the commands on the DB, the commands are just listed and no command is run. This is good for when you are setting up the schema of the DB and you want to make sure you have the fields correct.

So, for example - to pull the interviews of the "Smoker Survey" which were uploaded/modified in the last 48 hours into a SQL database called "ReadReplicaDB" which sits in the "db.mycompany.com" server. In this example we only pull 2 variables, the AGE and GENDER variables:


SurveyToGoReadReplicaCreator.exe /surveyid=7bd221bc-e0f3-45f4-b9ae-891d98fd0d47 
/userid=f150fc49-3a14-4ecb-9314-ca04698a9579/mark /password=1234 /window=48
/connstr="server=db.mycompany.com;uid=mydbuser;pwd=mydbpassword;database=ReadReplicaDB;Timeout=60000"
/tablename=SmokerSurveyTable /vars="age,gender"

The SurveyToGoReadReplicaCreator uses the "SurveyInterviewIDs" function combined with multiple calls to the "SimpleExport" function of the SurveyToGo REST API to retrieve the interview data of the uploaded/modified interviews.

 

Step 2: Add a Scheduled Windows Task To Automate the Data Pull

The next step is to add a Windows Scheduled Task to automate the running of SurveyToGoReadReplicaCreator every X hours/minutes. The rate in which to call the task would depend on how frequently you wish to receive newly added/modified interview data. We would recommend running it:

Run Interval Use Case
(Recommended)
2 hours
Standard Read-Replica. Combined with a window of 6 hours it means every interview that was modified/added in the past 6 hours will get pulled and refreshed the read replica every 2 hours. For Stand projects we recommend using this combination (2 hour interval / 6 hours window) for optimal performance. 
1 hour For higher refresh requirements, use a 1 hour interval + 6 hours window to pull data more frequently and refresh the data.
30 minutes For super high refresh requirements, use a 30 minute interval + 2 hour window to pull data super frequent. 
< 30 minutes Extreme cases. We would recommend using a maximum of 1 hour window when using an interval less than 30 minutes. 

 

The SurveyToGoReadReplicaCreator Source Code

Dooblo is providing the source code of the SurveyToGoReadReplicaCreator utility and it can be downloaded by clicking here: https://dblstg.dooblo.net/downloads/SurveyToGoReadReplicaCreatorSource.zip

The Source code has 2 projects:

SurveyToGoReadReplicaCreator Console Application

This is the shell application which parses the parameters and uses the class library to fetch the data. This application is also the one working against your provided SQL Server Database. 

replicaSource1.png

The "meat" of the application is in 2 parts. The first is calling the "GetRecentlyUpdatedInterviews" method of the class library to start the process of fetching the data:

replicaSource3.png

And the second is the implementation of the OnUpdatedInterviewsReceived event which is called when the data is fetched (it is called multiple times, each time with up to 99 interviews data):

replicaSource4.png

The code there deletes the interviews from the local database table provided and then runs the insert command for each of the interviews (and according to the variables needed).

 

SurveyToGoRESTAPIConnector Class Library

This class library is the piece of code that runs commands against the SurveyToGo REST API.

replicaSource2.png

The main methods provided by this library:

Method Name Description
GetRecentlyUpdatedInterviews This method is the "meat" of the library. It calls the "SurveyInterviewIDs" REST API function to retrieve the recently added/modified interview IDs of the survey. It then batches the interview IDs into 99 interviews per batch and calls the "SimpleExport" function for every batch. The "SimpleExport" gets the interview data of the interview IDs. 
GenerateDeleteommandFromInterviewIDs Utility function: This function will generate the "DELETE" SQL Server command associated with a list of interview IDs. It does not RUN the command, just generates it.
GenerateInsertCommandsFromInterviewTable
Utility function: This function will generate the "INSERT" SQL Server commands associated with a list of interview IDs. It does not RUN the commands, just generates them.
SqlServerExecNonQuery Utility function: this function will RUN a SQL server command against a SQL Server Database connection string.

 

Modifying the Source Code

You are welcome to download and modify the source code to fit your specific use case. 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.