Skip to main content
Glama
README.md
# ssis-export-toolkit

An MCP server that lets an AI assistant (Claude) create, deploy, schedule and run **SSIS packages** that move SQL Server tables to
**Azure Blob Storage** as CSV. Governed by design: one installation per SQL Server VM, one MCP per person, every action signed and
policy-checked.

You give orders in plain language ("export `dbo.Orders` to Blob Storage every day at 03:00"). The assistant turns them into a
declarative spec, generates the `.dtsx` packages and the project, and a small signed-request executor on the VM deploys them to
SSISDB, runs them, verifies the result and creates the SQL Agent job. Nobody runs scripts by hand except the one-time installation by
the administrator.

```
Claude + ssis-builder MCP (your PC) --signed request--> Blob Storage (ssis_control/) <--pull every minute-- executor (VM) --> SSISDB / SQL Agent
```

## Why

- **Repetitive packages, generated.** Export packages differ only in table, columns, types and file name. A spec describes the
  interface; the generator renders the package deterministically (same input, same output, diff-friendly).
- **Safe by construction.** No secrets in packages or specs; the storage key lives in the VM's DPAPI store. Source queries must be
  `SELECT`-only. Requests are Ed25519-signed per person and checked against a policy that only an administrator can change.
- **Auditable.** Every action records who did it (executor log, request status, job description).

## Who does what

| Role | What | Guide |
|---|---|---|
| Administrator (once per VM) | install the executor, store the storage key, authorise colleagues, own the security rules | [docs/ADMIN_GUIDE.md](docs/ADMIN_GUIDE.md) |
| User (once per PC) | clone, run `scripts/ssis_builder_setup_pc.ps1`, send your public key to the administrator | [docs/USER_GUIDE.md](docs/USER_GUIDE.md) |
| Everybody, on each release | `git pull`; VM-side changes need the administrator | [docs/UPGRADING.md](docs/UPGRADING.md) |

## Tools (MCP)

- Specs and packages: `ssis_spec_create`, `ssis_interface_spec_create`, `ssis_validate`, `ssis_generate_export`, `ssis_generate_bundle`.
- VM operations: `ssis_executor_status`, `ssis_deploy`, `ssis_configure`, `ssis_run`, `ssis_verify`, `ssis_cleanup`.
- Jobs: `ssis_schedule`, `ssis_job_enable`, `ssis_job_status`, `ssis_job_delete`.
- Administration and identity: `ssis_executor_installer`, `ssis_invite_colleague`, `ssis_my_public_key`.

## Safety model (short)

Allowed operations, databases, object-name prefixes and containers are defined in a policy that lives on the VM. A request is
rejected unless its signature verifies, it has not expired (max 60 min), it was never seen before (replay protection) and it passes
the policy. Details: [docs/executor.md](docs/executor.md).

## Status

Early-stage. The spec, generators, signing and executor logic are covered by unit tests (simulated channel and SSISDB). Generated
`.dtsx` files cannot be validated without SSDT/`dtexec`: run the first deployment against a test server. Imports (Blob Storage ->
table) are specified but the import generator is not built yet; see [package generators](docs/package-generators.md).

## Develop

```powershell
python -m venv .venv; .\.venv\Scripts\pip install -r requirements.txt
.\.venv\Scripts\python -m unittest discover -s tests
```

Docs: [package generators](docs/package-generators.md), [interface spec v2](docs/interface-spec-v2.md), [executor](docs/executor.md).

## License

MIT, see [LICENSE](LICENSE).