ETL_003: Export of ANL Fulfilment Files

1.0 Introduction

This document outlines an updated solution for the validation and export of ANL fulfilment files to fulfilment vendors every Monday, Wednesday and Friday.

Background

Due to limitations with current CIS processes, the output files need to be manipulated and validated before they can be exported to the fulfilment vendors.
The output files from CIS are formatted and structured for the previous vendor, Dataforce. These outputs consist of multiple files, each containing 170+ columns.
There is a need to reduce the number of files exported and consolidate to a single file per vendor and condense data to include only data that is relevant to the vendor.
Two vendors are currently used by DUK for fulfilment:
- Mosaic - Issue the ANL letters and also the Welcome Packs for Professional Members.
- Go Inspire (Direct Link) - Issue the Welcome Packs to new member sign-ups.
The updated solution involves several components working together, including a series of PowerShell scripts, SQL-stored procedures, JSON configuration files, email notifications, exception logging, and a SQL Server Agent Job responsible for scheduling and initiating the workflow.

Note: The transition of Go Inspire to the new consolidated file format and system is currently on hold, pending the finalisation of upcoming changes for Go Inspire's fulfilment by the Membership team.

For the interim, Go Inspire will continue to receive fulfilment files in the existing format and through the current system and SQL Agent Jobs:

ctq_Mosaic_Collate_and Stage_tri-weekly_fulfilmentfiles
ctq_Mosaic_GoInspire_upload_TriWeekly_FinFulFileFor_Third_parties

1.1 Intended audience:

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

1.2. 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.
Unknown Address: Refers to an internal issue within CIS where an Unknown Address status can sometimes be assigned in error, causing it to override a valid address on the contact record.

2.0 Overview of the ETL Process

2.1 Process description:

The process automates the movement, validation, reformatting and export of fulfilment files to the DUK SFTP for collection by fulfilment vendors.

2.2 High-level process flow diagram:

Fulfilment Files Export - High-level process view diagram

3.0 Detailed ETL Process Steps

This section outlines the main steps involved in the process.

3.1. Extract of CIS-Generated Fulfilment Files:

The first step is the extract and download of fulfilment files for pre-processing from a remote Linux server to a local Windows machine using WinSCP.

3.2. Validation checks:

A SQL-stored procedure is executed to perform various validation and processing tasks on the financial fulfilment files.
The procedure checks the data and identifies any invalid entries, such as duplicates or cancelled memberships.
Invalid entries are logged in an Audit Log table.

3.3. Formatting and Consolidation of Files:

The multiple fulfilment files are consolidated into a single file for export.
Vendor-specific configuration files are used to determine the folder paths for the input and output files and the required headers to filter the data for output.
Each row in the consolidated data is checked against the Audit Log table and for Unknown addresses.
- Exceptions are diverted to an exception file for manual review.
The consolidated file (minus any exception rows) is placed in a folder for the final stage.
The script also moves the original input files and control files used to an archive folder for backup purposes.

3.4. Upload of the file to the remote SFTP server:

The script connects to the SFTP server and uploads the consolidated file to the remote folder for collection by the fulfilment vendor.

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

30+ days old files related to this process on the SFTP server are deleted

3.6. Send a success notification email:

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 files:

DUK Linux machine: /u02/DATA/dataforce2/FTP/

Schedule: Files are available Monday, Wednesday and Friday by 02:30

4.2 Working folders:

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\02_Staging

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\03_Consolidated_Output

4.3 Archive folders:

A repository for raw and processed data files -

Raws folder -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\01_RawFiles

Processed files folder -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\04_SFTP_Output_Archive

4.3 Exception folder:

Files with exception rows for manual review and resolution -

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\05_Exception_Files

4.5 Logs:

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

\\gs1dukfilv01\shared$\CIS_Support\ETL\Fulfilment\ETL_003\_Logs

4.6. Destination SFTP server:

Server URL: sftp.diabetes.org.uk

Remote destination folders:

Mosaic: /Usr/Mosaic/_OUTBOUND_DATA/ANL_Fulfilment/

4.7. Powershell scripts:

Main script:

Drives the overall process.

\\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_003_00_ControlScript.ps1

Global configuration file:

Stores configurations common to all tasks.

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

Task-specific configuration files:

\\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_003_FulfilmentFileProcessing\ETL_003_TaskConfig.json
\\gs1dukmsqlv03\Powershell\ETL\Tasks\ETL_003_FulfilmentFilesPreProcessing\ETL_003_TaskConfig_Mosaic.json

4.8. SQL Agent Job:

Server: gs1dukmsqlv03

Job name: job_ETL_003_ANL_FulfilmentExport

Schedule: Every Monday, Tuesday and Wednesday at 06:30

5.0. SLAs:

For Mosaic, files are to be delivered by 11:00 a.m. each Monday, Wednesday and Friday.
If there is an issue or if no files are to be delivered then inform Niall Durdin so he can notify Mosaic.

6.0 Email Notifications and Troubleshooting

This section provides an overview of the various automated email notifications sent by the process. It also outlines the troubleshooting steps to be followed when an error alert is received.

The email notifications range from the successful completion of tasks to various error conditions.

They are categorised into the following groups:

Success Emails
Exception Emails
Error Alerts
SQL Job Failure Alerts
No Email Received

Recipients for the email notifications are as follows:

External: N/A

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

Alerts: michael.geelan@diabetes.org.uk,cissupport@diabetes.org.uk, helpdesk@duk844.freshservice.com (Exception Emails and Error Alerts)

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

6.1. Success Emails:

The email is sent to internal contacts to indicate successful process completion.

Actions:

No action is required.

6.2.Exception Emails:

These emails are sent when exception rows have been found and diverted to a file for review. A corresponding service desk ticket is also raised on iService.

Actions:

Direct action is required.

Review the exception file and the attached log file, and the record on CIS.

The screenshot below provides an extract from an example log. It shows a row was diverted from the file for Mosaic due to an Unknown Address.

The record on CIS would need to be reviewed and the address in the exception file corrected and placed directly on the SFTP.

6.3. Error Alerts:

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

Actions:

For SFTP connectivity issues, check the network and server status.
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 Mosaic and DUK stakeholders there might be a delay.

6.4. SQL Job Failure Alerts:

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

Actions:

Check the job logs for job_ETL_003_FulfilmentFileProcessing. 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)

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

7.0. Check Mailing Document Production Job on CIS Live

select *
from job_schedule
where job_desc = 'Mailing Document Production'
order by job_number DESC;

8.0 Maintenance

JSON configuration files are used to store and manage shared and task-specific configurations and settings.

[TO DO] Add a link to a generic JSON monitoring and troubleshooting guide.

8.0 Appendices

9.0 Document Properties

Property Value Version
1.01 Date Created 2023-04-14 Last updated 2023-09-30 Author Michael Geelan Reviewers
Approval date