Overview

When integrating SurveyToGo with third-party systems, dashboard portals, or reporting tools, direct SQL access to interview data may be required so external systems can retrieve and process survey responses.

If access is only needed for tabulated interview results, consider using the Tabulation REST API, which is specifically designed for retrieving tabulated survey data.

This article explains how to use the SurveyToGoReadReplicaCreator command-line tool to automatically retrieve recently uploaded or modified interview data from a survey and synchronize it with a SQL Server table.

SurveyToGoReadReplicaCreator tool download: 

https://dblstg.dooblo.net/downloads/SurveyToGoReadReplicaCreator.zip

 

Step 1: 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 a SQL server table (in this article the "Read Replica table"). 

 

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.

For example, the tool can retrieve interviews from the “Smoker Survey” uploaded or modified within the last 48 hours and sync selected variables (e.g., AGE and GENDER) into the “ReadReplicaDB” SQL database hosted on “db.mycompany.com”.

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

Adding a Windows Scheduled Task will 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.

Windows Task Recommendations

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

 

SurveyToGoReadReplicaCreator Source Code

 

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. 

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

 

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