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.
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.
Comments
I don´t know if there is a git to make a pull request, so I´ll leave the fix here for future clients reference:
In file Connector.cs I had to change 2 things:
1. In GetRecentlyUpdatedInterviews method the url had "dateEnd{2}" instead of "dateEnd={2}"
2. In GetRecentlyUpdatedInterviews method the date.toString() is not in the correct format for the url. Try using HttpUtility.UrlEncode(dtStart.ToString("o"));
P.S Remember to add a column "SbjNum" to your database table (e.g. ALTER TABLE table_name ADD SbjNum int;).
Please sign in to leave a comment.