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 SurveyToGo:ReadReplicaCreator 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 SurveyToGo:ReadReplicaCreator Command Line tool

The SurveyToGo:ReadReplicaCreator 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 SurveyToGo:ReadReplicaCreator command line tool from here: https://dblstg.dooblo.net/downloads/SurveyToGo:ReadReplicaCreator.zip 

The SurveyToGo:ReadReplicaCreator 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:

The SurveyToGo:ReadReplicaCreator 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 SurveyToGo:ReadReplicaCreator 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 SurveyToGo:ReadReplicaCreator Source Code

The Source code has 2 projects:

SurveyToGo:ReadReplicaCreator 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. 

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:

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):

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

 

SurveyToGo:RESTAPIConnector Class Library

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

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.