ETL - Qualtrics - Surveys (SQL Agent Job)
Last Updated 5/24/2021
The SQL Agent Job for Qualtrics Surveys loads survey response data from Qualtrics related to COVID. It runs every 20 minutes because the responses power dashboards that need to be as near real-time as possible.
The primary purpose of this job is to load data from Qualtircs surveys related to COVID and populate COVID related tables every 20 minutes. The details of downstream applications are in the next section
SQL Agent Job in the dataMarq data warehouse. It runs packages in the staging package ETL-Qualtrics along with a variety of stored procedures – detailed below – to build COVID tables.
The jobs in this project run as part of multiple SQL Agent jobs (full dataMarq schedule)
ETL - Qualtrics Surveys – Daily - Everyday every hour from 7am to 6pm
ETL - Qualtrics Surveys -- Full – Not scheduled, can be run ad-hod
STEP 1: Execute Survey Load Packages
This step runs the qx_load_surveys package in the ETL-Qualtrics project. This populates all of the qualtrics schema tables with the latest responses in the datamarq_staging database.
STEP 2: Execute Lists Proc
This procedure populates the table ops.d_covidcheq_list. This table has all of the people and data needed for the COVID Cheq survey directory. It is used to both push data to Qualtrics for people who should be added/disabled, it also contains data on last responses to track delinquency. This table also powers the COVID Cheq dashboards used by faculty to track student responses status.
STEP 3: Execute OIRA Procs
These procedures populate tables that OIRA uses for COVID tracking. They are all based on survey responses and fairly straightforward.
Intermittent Failure on Qualtrics Survey loadOccassionally the integration with Qualtrics will fail due to a connection error or a timeout on the Qualtrics side. We use the API to interact with Qualtrics and they sometimes throttle the connection. If there are more than two consecutive failures in this job it is usually something on our end, not the Qualtrics. But an occassional faillure is to be expected.