# Etl Assessment

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

- Skill: `snowflake-labs/etl-assessment` (Agent Skill, multi-file: 11 files)
- Install (CLI): `npx skillmds@latest add snowflake-labs/etl-assessment`
- Raw SKILL.md: https://api.skillmd.com/api/skills/snowflake-labs/etl-assessment/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: Proprietary. See License-Skills for complete terms
- Author: snowflake-labs (https://skillmd.com/u/snowflake-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/snowflake-labs/etl-assessment

---


# 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](#step-1-locate-input-files-auto-detected) |
| `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](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**:

```json
{
  "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](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):

``` bash
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`:

``` bash
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](references/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:
```bash
scai assessment etl summary
```

2. Read the guide: [references/ai_summary_guide.md](references/ai_summary_guide.md)

3. Generate summary file: `ai_ssis_summary.html`

4. Register the summary in the JSON:
```bash
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