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_analyzer reads only local CSVs and .dtsx files.

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>".

Rules

1. Use ONLY Provided Scripts

   • ONLY use the provided CLI commands: python -m scai_assessment_analyzer
   • 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	The --etl-replatform-sources-path recorded by convert (or <project_dir>/source/etl/, falling back to whatever was passed to scai code convert)
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, the conversion either skipped ETL or didn't include the --etl-replatform-sources-path flag — return to the parent and ask the parent to re-run convert with ETL inputs. Do not ask the user to upload paths.
• Confirm the SSIS source directory contains .dtsx files.

---

Step 2: Generate JSON Analysis

Run with the auto-detected paths from Step 1:

uv run python -m scai_assessment_analyzer <ETL.Elements> <ETL.Issues> <SSIS_SOURCE_DIR> <OUTPUT>

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:

uv run python -m scai_assessment_analyzer etl <JSON_PATH> 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:

uv run python -m scai_assessment_analyzer etl <JSON_PATH> ai-summary <HTML_PATH>

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