Last Updated 5/24/2021

Overview
Location
Schedule
Details
Change Log
Troubleshooting Notes

Overview:

The SQL Agent Job for D2L stages and processes data needed for D2L related tables and dashboards. This is the main job that stages D2L data and it runs incrementally during the week and runs a full refresh of all staging tables on Sunday nights when the D2L data hub produces full files.

Location:

SQL Agent Job in the dataMarq data warehouse.

Schedule:

The jobs in this project run as part of multiple SQL Agent jobs

ETL - D2L - Full – Monday at 1am

ETL - D2L - Incremental – Everday Except Monday at 1am

Job Details:

STEP 1: Run Master D2L Packages

This step runs the master package in the ETL-D2L project which stages all the required data from D2L into the d2l schema in datamarq_staging.

STEP 2: Run Procs

This step runs the procedures related to populate d and f tables. 

sp_d_organizations

sp_d_competency_objects

sp_f_competency_assessments

sp_f_scorm_activities

 

STEP 3 and 4: Success or Failure Email

Depending on the status of the job, the job will exit sending a success or failure message to the BI Team.

ops.sp_etl_send_email 
This is our custom notification proc that gets passed the job id and looks up the success/failure status and sends the appropriate email and text message to the BI team.  

Change Log:

Date	Developer	Change Record
6/10/2021	N/A	Initial Documentation

Troubleshooting Notes: