etl-assessment

name	etl-assessment
description	Analyze SSIS packages from SnowConvert ETL.* outputs and source .dtsx files. Classifies packages, scores migration complexity, and produces JSON for the parent assessment multi-report.
parent_skill	assessment
license	Proprietary. See License-Skills for complete terms

SSIS Assessment

SnowConvert AI migrates SSIS packages to Snowflake. This skill analyzes packages from their source code and SnowConvert assessment CSV reports to generate detailed migration analysis including package classification and complexity assessment.

---

Sub-Agent Mode

When invoked from a parent skill (e.g., assessment/SKILL.md) as a sub-agent, the parent provides a context block with the fields below. The review_mode field controls whether the per-package review loop runs.

Field	Required	Notes
project_dir	yes	absolute path to the SCAI project root
output_dir	yes	typically <project_dir>/assessment/ssis
etl_replatform_sources_path	no	absolute path to the SSIS .dtsx source directory; falls back to auto-detection per Step 1
review_mode	yes	generate-only, auto-review-all, or skip

On entry: call the configure MCP tool with project_dir from the context block. Snowflake credentials are not required — scai assessment etl generate reads converted CSVs and SSIS .dtsx sources.

Branching by review_mode:

• generate-only — run Steps 1–2 (locate inputs + generate JSON). Return the etl_assessment_analysis.json path; do not run per-package analysis or the AI summary.
• auto-review-all — run all four steps (locate inputs, generate, analyze every package per references/analyze_ssis_package.md, draft the AI HTML summary, register it). Stop only when stats reports no pending packages.
• skip — return immediately with "status": "skipped".

On completion, return JSON only:

{
  "sub_skill": "etl-assessment",
  "status": "ok",
  "output_json": "<abs path to etl_assessment_analysis.json>",
  "summary": "<one-line: total packages, classified count, pending count>",
  "error": null
}

On skip: "status": "skipped", "output_json": null. On failure: "status": "error", "error": "<message>".

Important Notes

• No help command: The scai assessment etl commands are hidden (AI-agent-only, like sql-dynamic). Running scai assessment etl <command> --help returns "No help available". Commands are documented in this skill file only.
• Auto-detection: All C# commands (generate, pending, scan-package, stats, summary, update, ai-summary) auto-detect the JSON path and SSIS source directory from the configured project context. You do not need to pass explicit paths.
• Single argument for scan-package: The scan-package command takes ONLY the package path (relative to the SSIS source directory). Do NOT pass a second argument with an absolute path. Example: scai assessment etl scan-package 'MyPackage.dtsx' (NOT ... 'MyPackage.dtsx' '/full/path/to/MyPackage.dtsx').

Rules

1. Use ONLY Provided Scripts

   • ONLY use the provided scai assessment etl commands (generation) and Python reader commands (analysis tracking)
   • DO NOT create custom scripts to read or manipulate the JSON file
   • DO NOT use grep, jq, or other tools to parse the JSON directly
   • All JSON interactions MUST go through the provided CLI

2. Quality Over Speed

   • Each package deserves thorough, individual analysis
   • Write specific, detailed analysis for each package
   • DO NOT create batch analysis regardless of package volume
   • DO NOT skip analysis steps to save time

3. Follow ONLY Provided Analysis Methods

   • Use ONLY the workflow in references/analyze_ssis_package.md

4. Complete ALL Steps

   • Step 4 (AI Summary) is MANDATORY before finishing
   • DO NOT skip to report generation without completing all steps
   • Every checkpoint in the workflow must be completed

5. Report Generation via Parent Skill ONLY

   • The multi-report generator in the parent skill (../SKILL.md) is the ONLY approved method
   • Provide the --ssis-json parameter pointing to the etl_assessment_analysis.json file generated by this skill

---

Workflow

Copy this checklist and track your progress:

Analysis Progress:
- [ ] Step 1: Locate and Validate Input Files
- [ ] Step 2: Generate JSON Analysis
- [ ] Step 3: Analyze SSIS packages (sub workflow)
- [ ] Step 4: Draft AI summary (HTML AI summary)

Step 1: Locate Input Files (auto-detected)

Do NOT prompt the user for paths. Inputs are resolved automatically from project_dir configured by the parent assessment skill:

Input	Auto-resolution
ETL.Elements.csv	Latest <project_dir>/reports/SnowConvert/ETL.Elements.*.csv
ETL.Issues.csv	Latest <project_dir>/reports/SnowConvert/ETL.Issues.*.csv
SSIS source dir	<project_dir>/source/_etl/
Output dir	<project_dir>/assessment/ssis/ (create if missing)

Validation (silent — only surface a problem to the user if validation fails):

• Confirm both ETL.* CSVs exist. If they don't, ETL was not converted — return to the parent and ask the parent to re-run register (code add) then convert. Do not ask the user to upload paths.
• Confirm the SSIS source directory contains .dtsx files.

---

Step 2: Generate JSON Analysis

Run the C# assessment generator from the project root (it auto-detects all paths from the configured project):

scai assessment etl generate

This writes two timestamped files to <project>/artifacts/assessment/etl/:

• etl_assessment_analysis_<timestamp>.json — the main assessment data
• dag_model_<timestamp>.json — control-flow and data-flow graph data for DAG rendering

Immediately after generation, render the DAG HTMLs from the newest dag_model_*.json:

dag_model=$(ls -t <project>/artifacts/assessment/etl/dag_model_*.json 2>/dev/null | head -1)
if [[ -n "$dag_model" ]]; then
  uv run --project ai ai/plugin/skills/migration/assessment/etl-assessment/scripts/dag_renderer/render_dags.py "$dag_model"
else
  echo "No dag_model.json found; skipping DAG render (expected if no control-flow or data-flow components)"
fi

The renderer writes dags/*.html interactive visualizations to the same output directory.

Step 3: Analyze SSIS Packages

This step is a sub workflow to analyze packages individually. After this, continue with step 4.

To analyze SSIS packages, follow the instructions of the workflow of this reference file: reference/analyze_ssis_package.md

---

Step 4: Draft AI Summary

DO NOT SKIP THIS STEP. The AI Summary is required before finishing the assessment.

Required Actions:

1. Run the summary command to get all signals:

scai assessment etl summary

2. Read the guide: references/ai_summary_guide.md

3. Generate summary file: ai_ssis_summary.html

4. Register the summary in the JSON:

scai assessment etl ai-summary <HTML_PATH>

Note: The summary and ai-summary commands use the C# CLI to read and mutate the assessment JSON (auto-detected from project context).

Verification Checklist:

• ai_ssis_summary.html file exists
• Summary registered with ai-summary command

Completion: Report to user:

• Total packages analyzed
• JSON file location: <output_path>/etl_assessment_analysis.json
• Go to main workflow in parent skill