ETL_007: DUK to Sterling Lottery HouseFile

1.0 Introduction

This document outlines the process of a weekly export of a basic house file from DUK to Sterling. It covers the extraction of contacts and lottery entry numbers, data exclusions, key locations, objects, and timings. Additionally, it provides lists of contacts for internal and external notifications and offers basic troubleshooting guidance.

Background

The house file for Sterling provides DUK contact numbers for lottery players with surname, postcode and associated Sterling Lottery Entry Number.
This data supports the process for the output of mailing history files that are sent from Sterling to DUK.
The mailing history file from Sterling provides a DUK contact number and a Sterling Weekly Lottery reference (STWL) enabling the matching to a contact record on CIS and load of the associated mailing communication record.

1.1 Intended audience:

CIS Support team members, technical stakeholders, and the non-technical stakeholders responsible for the business side of the process.

2.0 Overview of the ETL Process

This section presents a brief outline of the process flow.

2.1 High-level process flow diagram:

Weekly DUK House File to Sterling - High-level process view diagram

3.0 Detailed ETL Process Steps

3.1. Extract data from CIS:

A stored procedure is called by the Powershell script to extract contact numbers and lottery entry numbers from the data warehouse (DiabetesDWH)

3.2. Generate file for export:

The extracted data is formatted and saved as a CSV file.

3.3. Upload the file to the remote SFTP server:

The script connects to the SFTP server and uploads the CSV file from the local folder to the remote folder.

3.4. Archive the file on the local server:

A copy of the uploaded file is retained on the local server.

3.5. Clean up of old files on the remote SFTP server:

30+ days old files on the SFTP server are deleted.

3.6. Send success notification emails:

Upon completion, the script sends a success notification email to the recipients specified in the task configuration file.

4.0 Summary of Key Locations, Jobs, and Timings

4.1. Source Data:

Data Source:

- Lottery entry numbers are found in the Communications/Documents section of CIS.

- Under File Notes, with topic type: Weekly Lottery Account Information and sub-topic type: Account Information Received.

Handling Multiple Entries:

- When a contact has multiple lottery entry numbers, the procedure retrieves the highest value, presumed to be the most recent.

Address Data:

- The postcode is taken from the Default address record for the contact.

Suppressions:

Exclusions applied:

Contact records with a status of ITDQ, ITMM, or REF are excluded.
Contact records with an ownership group of GDPR or REF are excluded.

4.1. Local file system:

Working folder:

Temporary storage space for data processing -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Sterling\HouseFile\Working

Archive folder:

A repository for processed data files -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Sterling\HouseFile\Archive

Logs folder:

Stores the PS and WinSCP logs for each run to aid in troubleshooting -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Sterling\HouseFile\Logs

4.2. Destination SFTP server:

Server URL: sftp.diabetes.org.uk

Destination folder: /Usr/sterlinglottery/_OUTBOUND_DATA/DUK_Sterling_HouseFile

4.3. Powershell scripts:

Main script:

Drives the overall process.

\\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_007_ExportHouseFileToSterling\PROD\ETL_007_ControlScript.ps1

Global configuration file:

Stores configurations common to all tasks.

\\gs1dukmsqlv03\Powershell\ETL\Shared\Config\GlobalConfigDiabetesETL.json

Task-specific configuration file:

Specific configurations for this ETL job.

\\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_007_ExportHouseFileToSterling\PROD\ETL_007_TaskConfig.json

4.4. SQL Agent Job:

Server: gs1dukmsqlv03

Job name: job_ETL_007_ExportHouseFileToSterling

Schedule: Every Tuesday at 07:30

5.0 Email Notifications and Troubleshooting

This section provides an overview of the various automated email notifications associated with the ETL process. The email notifications range from the successful completion of tasks to various error conditions.

Recipients for the email notifications are as follows:

External: hthomas@sterlinglotteries.co.uk, sharrison@sterlinglotteries.co.uk (Success Emails)

Internal: grace.akintokun@diabetes.org.uk, michael.geelan@diabetes.org.uk,cissupport@diabetes.org.uk (Success Emails)

Error Alerts: michael.geelan@diabetes.org.uk,cissupport@diabetes.org.uk, helpdesk@duk844.freshservice.com

SQL Job Failure Alerts: cissupport@diabetes.org.uk

Actions to be taken for the email notifications are given below. They are categorised as follows:

Success Emails
Error Alerts
SQL Job Failure Alerts
No Email Received

5.1. Success Emails

These notifications confirm the successful completion of the process.

Internal Contacts: The email sent to DUK staff includes an attached log file for reference, as shown in the following screenshot.

Generally, no follow-up action is expected upon receiving this notification.

External Contacts: The email sent to external contacts includes relevant DUK contacts in the CC field, as shown in the next screenshot.

5.2. Error Alerts

These error alerts are sent to internal staff when issues arise with the export process. A corresponding service desk ticket is also raised on iService.

Actions:

Review the PowerShell and WinSCP logs for additional information. Report findings to Michael Geelan (michael.geelan@diabetes.org.uk).

Note: If files are anticipated to be late, inform DUK stakeholders there might be a delay.

5.3. SQL Job Failure Alerts

This alert is dispatched to internal staff when the SQL job has failed. Depending on the error, it might be necessary to rerun the job.

Actions:

Check the job logs for job_ETL_007_ExportHouseFileToSterling'. Report findings first to Michael Geelan (michael.geelan@diabetes.org.uk).

Note: See article: SQL Server Agent Jobs: Monitoring and troubleshooting guide | Knowledge Base | iService - Your Information Systems Service Desk (diabetes.org.uk)

5.4. No Email Received

A lack of email alerts might indicate that the export process is not functioning as expected or that there are issues with the email system.

Actions:

Check the server status.
Check the status of the SQL Agent Job 'job_ETL_007_ExportHouseFileToSterling'.
Confirm if the latest files are in the correct location.
Check the JSON configuration files are in the correct location.
Check the email system status.
Review the SFTP destination folder.

For each step, report findings to Michael Geelan (michael.geelan@diabetes.org.uk).

6.0. Document conventions and terminology:

PS (PowerShell) - A task automation and scripting language.
ETL (Extract, Transform, Load) - A process to extract data, transform it, and then load to a target system.
SFTP (Secure File Transfer Protocol) - A secure protocol for transferring files over networks.
JSON (JavaScript Object Notation) - A lightweight data-interchange format.
WinSCP (Windows Secure Copy) - A software tool for secure file transfer between a local and a remote computer.

7.0 Document Properties

Property Value Version
0.02 Created 2023-10-02 Last updated 2023-10-12 Author Michael Geelan Reviewers
Approval date