CDC

ETF Job Creation: Problem & Solution Document

---

1. Problem Statement

Current Challenges in ETF Job Creation

• High Development Effort
  Creating new jobs, especially for new clients or new file formats, demands significant development time, including writing new scripts, packages, and stored procedures.

• Redundant and Repetitive Work
  Many jobs that are repetitive or similar require manual recreation, leading to repetitive coding and testing efforts.

• Limited Self-Service Capability
  Business or Operations (OPS) teams currently do not have the ability to configure or create jobs independently, making the process dependent on Development (Dev) and Quality Assurance (QA) teams and increasing turnaround time.

• Complex Job Modifications
  Changes to existing jobs, such as adding/removing fields or modifying configurations, involve manual updates to packages and stored procedures, impacting maintenance and agility.

• Scattered Configuration Management
  Job-related configurations (e.g., file paths, email distribution lists) are managed separately, increasing the risk of inconsistencies and complicating configuration updates.

---

2. Scope

In Scope

• Similar or repetitive reporting files for new Funds/Fund Families.
• Requests for a new type of file that is already generated for another Fund/Fund Family.

Out of Scope

• Addition of new fields in existing files for an existing Fund/Fund Family.
• Requests for reports with different field combinations, transformations, or formats than current reports.

---

3. Benefits of Current System Limitations

• Minimal changes to existing jobs reduce risk but limit agility.
• Business/OPS team lacks self-service capabilities for configuring jobs.
• Heavy reliance on Dev/QA teams for creation or replication of jobs.

---

4. Proposed Solution

Streamlined ETF Job Creation Process

To reduce development costs and repetitive work, the following improvements are proposed:

---

4.1 UI for Job Creation

• Develop a dedicated screen where Output ID/Job ID can be created directly via a user-friendly interface.
• This interface will replicate current SQL insert script functionality, eliminating manual script creation.
• It empowers Business/OPS teams to create or replicate job configurations quickly without Dev/QA involvement.

---

4.2 UI for SSIS Configuration Management

• Create a screen to centrally store all SSIS package-related configurations such as:

  • Output file paths
  • File names
  • Email distribution lists (DLs)
  • Other configurable settings

• SSIS packages will read configuration details directly from the database via this interface, replacing the need for multiple separate configuration files.

---

4.3 UI for Job Scheduling (Future Scope)

• Design a screen to allow job scheduling directly in SQL Server.
• This will require elevated permissions on the MSDB database.
• Enables Business/OPS teams to manage job schedules without manual SQL Server Agent setup.

---

5. Summary of Workflow Changes

Phase/Action	Current Process	Proposed Process
New Client Onboarding	Write DB scripts and packages manually	Use UI to insert Output ID & create job configurations
Job Creation for Existing Clients	Create packages, stored procedures, and configs manually	Use UI to select existing templates and configure jobs
Changes to Existing Jobs	Modify existing packages and update config files manually	UI allows configuration changes without code changes
Job Scheduling	Manual SQL Server Agent setup	UI-based scheduling for ease and automation

---

6. Expected Benefits of Proposed Solution

• Reduced Development Time: Automate repetitive steps and eliminate manual script creation.
• Empowered Business/OPS Teams: Enable non-technical users to configure and manage jobs independently.
• Consistency and Accuracy: Centralized configuration storage reduces errors and inconsistencies.
• Improved Agility: Faster onboarding for new clients and easier job modifications.
• Future Proof: UI-based scheduling adds flexibility and reduces dependency on DBAs.