SSIS Job Catalog
Superseded · v1
Point-in-time snapshot from the 2026-06-08 SSIS review meeting — the working reference at the time. The current catalog adds authoritative stored-procedure descriptions from the
Point-in-time snapshot from the 2026-06-08 SSIS review meeting — the working reference at the time. The current catalog adds authoritative stored-procedure descriptions from the
etl/ submodule plus the job dependency map → SSIS Job Catalog (current) →
Dashboard KPIs — 2026-06-08 ~9am MST
13
Total Jobs
1
Currently Running
1
Failed (24h)
93%
7-day Success Rate
2
Persistent Failures
All 13 Jobs
| Job | Last Run | Status | 30-day Runs | Success % | Avg Duration |
|---|---|---|---|---|---|
| Mileone_reward_member ThreeBirds.Analytics.Mileone.reward |
May 28, 11pm | FAILED | 20 | 0% | 4s |
| ThreeBirds.Analytics.BlueSky | Jun 7, 6:30pm | FAILED | 30 | 93% | 2h 37m |
| JuiceReporting_CDXPCustomers | Jun 7, 9pm | Succeeded | 7 | 71% | 7m 7s |
| ThreeBirds.Analytics.CRM | Jun 8, 8:01am | Running | 30 | 93% | 3h 20m |
| appointment-process ThreeBirds.Analytics.Appointment |
Jun 8, 5:30am | Succeeded | 31 | 97% | 2h 53m |
| Conquest_Mailing ThreeBirds.Analytics.Conquest |
Jun 7, 12pm | Succeeded | 30 | 100% | 57s |
| MileOne Weekly Unsubscribe | Jun 8, 6am | Succeeded | 60 | 100% | 2m 54s |
| ThreeBirds.Analytics.CustomerValue | Jun 7, 11:30pm | Succeeded | 30 | 100% | 8m 41m |
| ThreeBirds.Analytics.CVH | Jun 7, 4pm | Succeeded | 30 | 100% | 2h 4m |
| ThreeBirds.Analytics.DDE | Jun 8, 3am | Succeeded | 31 | 97% | 1h 54m |
| ThreeBirds.Analytics.Email | Jun 7, 12:30pm | Succeeded | 30 | — | 4h 25m |
| ThreeBirds.Analytics.LeadSource_LeadPerformance | Jun 7, 11pm | Succeeded | — | — | 1h 28m |
| ThreeBirds.Analytics.SmartSuppression | — | — | — | — | — |
Active Failures
Mileone_reward_member — 0% success over 20 runs
Last run was May 28. Nobody on the DAS team knows why it's still in the pipeline. The job runs in 4 seconds (no-op or failing immediately). Needs investigation before CDP integration planning.
ThreeBirds.Analytics.BlueSky — Duplicate object collision
93% success rate historically but currently failed. Classic SSIS truncate-rebuild failure: the job tries to recreate a table that was partially created in a prior run.
sp_juice_data_sync_bluesky_ssis: Error: Executing the query "EXEC sp_juice_data_sync_bluesky_ssis" failed with the following error: "There is already an object named 'JuiceReporting_CDXPCustomers' in the database."Fix: add
DROP TABLE IF EXISTS before recreate, or migrate to incremental upsert logic.
Architecture Notes
- › All jobs use a truncate-rebuild pattern — they drop/recreate target tables rather than upsert, creating fragility when a prior run left partial state.
- › Jobs run in waves from midnight through ~9am MST. CRM (~3h) and Email (~4h) are the longest-running jobs.
- › 3–4 offshore engineers manually monitor and remediate failures daily, with no alerting yet and limited documentation.
- › The CDXP Job Status Dashboard (localhost:3000) was built ~1 week before this session — first centralized visibility layer added to the pipeline.
- › Job DAG relationships are undocumented — the dashboard shows job health but no dependency graph exists.
- › AWS Glue was considered and rejected — it would relocate the problem without fixing truncate-rebuild fragility.
Stored Procedure Inventory — etl/ submodule · authoritative
Source code for 5 of 13 jobs is in etl/SSIS/ (Database-Processes repo, added as a submodule 2026-06-09). The remaining 8 jobs have no stored procedures committed — descriptions from Rick/Eng still needed.
| SSIS Job | Module Path | What It Does | Key Tables |
|---|---|---|---|
| ThreeBirds.Analytics.CRM | etl/SSIS/CRM/ | Processes CRM data from eLeads, DealerSocket, VinSolutions, DriveCentric, Momentum. Per-provider prestaging → merged staging → main CRM table. Also runs daily loads (RR/VS clients), unsubscribes, and lead source/status lookups. | prestaging_CRM_*, staging_CRM, CRM |
| ThreeBirds.Analytics.CVH | etl/SSIS/CVH/ | Merges CRM and DMS staging data into centralized Customer Vehicle History (CVH) tables — the current platform-wide unified customer record used by analytics, dashboards, and campaign targeting. CDP identity resolution will replace this join. Runs after CRM and DMS. | staging_CVH, CVH, CVH_List |
| appointment-process ThreeBirds.Analytics.Appointment |
etl/SSIS/DMS/ | Processes DMS data — appointments, sales, service records, address mapping, CRM-DMS recipient matching, declined services. Prestaging → staging → target DMS tables. Also handles Alteryx address enrichment and cellphone normalization. | prestaging_DMS_*, staging_DMS, DMS_Fact_* |
| ThreeBirds.Analytics.Email | etl/SSIS/Email/ | Processes email campaign data from the legacy platform: sends, recipients, opens, clicks, bounces, links, GA/customer journey tracking, 60-day matchbacks, MileOne email summaries, and JuiceReporting OEM/parent group inserts. | Email_Sends, Email_Events, Email_Recipients, Email_Links, staging_Email_* |
| ThreeBirds.Analytics.BlueSky | etl/SSIS/BlueSky/ | Generates data for BlueSky dashboards: contacts from legacy email platform, customer vehicles, engagement stats, vehicle lifecycle position (predicted mileage / next service), product engagement, stream campaign messages, and messaging-eligible leads. | Analytics_BlueSky_Contact, Analytics_BlueSky_Engagement, Analytics_BlueSky_LifecyclePosition, Analytics_BlueSky_StreamMessages |
8 jobs not in repo: Mileone_reward_member · JuiceReporting_CDXPCustomers · ThreeBirds.Analytics.CustomerValue · ThreeBirds.Analytics.DDE · ThreeBirds.Analytics.LeadSource_LeadPerformance · ThreeBirds.Analytics.SmartSuppression · Conquest_Mailing · MileOne Weekly Unsubscribe
Inferred Job Dependencies — partial · from stored procedure analysis
Derived from stored procedure inputs/outputs. The full DAG for all 13 jobs remains undocumented — this covers the 5 jobs with source code only.
- ThreeBirds.Analytics.CRM → ThreeBirds.Analytics.CVH CRM staging tables are a direct input to CVH merge
- appointment-process (DMS) → ThreeBirds.Analytics.CVH DMS staging tables are a direct input to CVH merge
- ThreeBirds.Analytics.Email → ThreeBirds.Analytics.BlueSky BlueSky contact/engagement data draws from legacy email platform tables
Action Items
- HIGHInvestigate why Mileone_reward_member has 0% success rate — Rick / Eng team
- HIGHFix BlueSky "already an object" failure — add DROP IF EXISTS or switch to upsert — Eng team
- HIGHImplement failure alerting — engineers are silently fixing with no notifications — Rick / Leo's team
- MEDGet job descriptions for the 8 jobs not in etl/ submodule (CustomerValue, DDE, LeadSource_LeadPerformance, SmartSuppression, Conquest_Mailing, MileOne Weekly Unsubscribe, JuiceReporting_CDXPCustomers, Mileone_reward_member) — Rick
- MEDComplete job dependency DAG — map remaining 8 jobs' inputs/outputs and confirm CRM/DMS → CVH run order — Rick / Eng team
- MEDAirflow migration replaces SSIS — this repo becomes the authoritative source for stored procedure logic during migration — Conflict team
- LOWAudit unused databases (BirdBath, DigitalBundle) — dependency-map before deleting — Eng team
Source Captures
Screencaps extracted from the session recording by Planopticon. Data in this artifact was verified against these frames. Click any image to view full resolution.
