data_dp_ssis2sp 0.12.29

Convert SSIS data-flow transformations into consolidated, behaviour-equivalent T-SQL.

data_dp_ssis2sp

Convert SSIS (.dtsx) data-flow transformations into consolidated, behaviour-equivalent T-SQL: one WITH … INSERT INTO … SELECT statement per destination, where each transformation is a CTE.

.dtsx  ──parse──▶  IR  ──graph──▶  DAG  ──transpile──▶  CTEs  ──generate──▶  T-SQL

Install

Prerequisite: uv.

uv sync            # installs data_dp_ssis2sp + every dependency group

macOS needs brew install unixodbc once for pyodbc. Skip the differential validation stack with uv sync --no-group validation.

Packaging & building

The project uses the setuptools build backend (pyproject.toml); uv drives everything:

uv sync                       # dev setup: editable install + all dependency groups
uv lock                       # refresh uv.lock after changing dependencies
uv build                      # build sdist + wheel into dist/

uv build produces:

dist/data_dp_ssis2sp-0.1.0.tar.gz             # sdist
dist/data_dp_ssis2sp-0.1.0-py3-none-any.whl   # pure-Python wheel

The wheel installs three console scripts: data_dp_ssis2sp (the CLI), data_dp_ssis2sp-ui (the Textual terminal UI), and data_dp_ssis2sp-web (the browser UI). The interactive front-ends are separate entry points — the bare data_dp_ssis2sp command is CLI-only and prints help when given no subcommand. To install the built wheel into another environment:

uv pip install dist/data_dp_ssis2sp-0.1.0-py3-none-any.whl   # into the active venv
uv tool install dist/data_dp_ssis2sp-0.1.0-py3-none-any.whl  # as a global uv tool

The version lives in pyproject.toml ([project] version) — bump it there before building a release. dist/ and build/ are gitignored.

Usage

The CLI is environment-driven: every operational parameter is an environment variable; the command line carries only per-run path operands.

Command	What it does
convert <dtsx> [-o OUT]	one .dtsx → consolidated T-SQL (stdout or -o; always a raw batch, never a proc)
inspect <dtsx>	print the parsed component graph and exit
convert-tree <in> <out>	recursively convert a directory of .dtsx into a mirrored .sql tree; CONVERT_NO_ORCHESTRATOR=true opts out of the collapsed main proc
extract-agent-jobs	read msdb.dbo.sysjobs* over ODBC and emit one JSON file per SQL Server Agent job (sequence-preserving, for the ADF pipeline converter); PROC_MANIFEST_PATH rewrites SSIS steps to call the converted procedures
create-agent-jobs [--procs-dir D] [--out-dir D] [--server-name S] [--job-owner L] [--category C] [--no-logging]	generate one idempotent msdb Agent-job .sql per orchestrator proc from a converted --split-by-flow tree — one step per data-flow proc in EXEC order; each step is wrapped in TRY/CATCH run-logging to [<schema>].[AgentJobRunLog] (with a companion _ddl/ table script per resolved database) unless --no-logging reverts to bare EXEC; no DB work
deploy-agent-jobs [--jobs-dir D] [--server-role R]	deploy the emitted Agent-job .sql scripts (jobs/*.sql, excluding the _ddl/ subtree) against msdb on the resolved host for --server-role target (default; TARGET_SERVER) or source (SOURCE_SERVER)
extract-packages	connect to a SQL Server store (Windows auth) and write every stored SSIS package to disk as .dtsx; auto-detects the SSISDB catalog, falling back to the legacy msdb store; PACKAGE_EXPANDED=true also writes the project files
extract-ispacs	download every .ispac project archive from the source SSISDB catalog verbatim (no unzipping), laid out as <folder>/<project>.ispac under <base>/ispacs/; scoped by the same package_extract_scope allowlist as extract-packages (--get-all bypasses it)
pipeline	run the full extraction chain:extract-packages, then convert-tree into stored_procedures/, then extract-agent-jobs with PROC_MANIFEST_PATH wired to stored_procedures/_proc_manifest.json (mirrors run.bat pipeline); stops at the first failing stage
emit-adf [--jobs-dir D] [--out-dir D]	convert the per-job JSON from extract-agent-jobs into ADF Git-format pipeline + trigger JSON; SSIS steps execute the existing SSISDB catalog packages via generated T-SQL. Exit 0 = clean, 2 = error/FAIL, 3 = converted but ≥1 step's package is not in the catalog (/FILE//SQL steps — loud stderr banners + Fail placeholder activities). Any @Project.manifest found under the packages dir is parsed automatically: SSIS execution parameters are injected into catalog activities, _adf_param_report.json is written, and it exits 2 (FAIL) when any required parameter is missing, has an unknown type code, or is ambiguous across packages — see Execution-parameter injection
verify-config	print the configuration resolved from the environment and exit
show-settings	print the resolved settings as a formatted rich table and exit
generate-linked-services [--out-dir D]	generate ADF linked-service JSON (Key Vault, SQL, storage, SharePoint) from GlobalSettings into linkedService/*.json
emit-adf-sharepoint --out-dir D	emit ADF Git-format artifacts for SharePoint list migration (one pipeline per subsite, driven by GlobalSettings); see SharePoint list migration
load-stored-procedures [--procs-dir D]	connect to the SQL Server and execute the .sql at --procs-dir — a single .sql file or a directory (recursed); creates the migration schema if absent. Upload generated SharePoint load procs by pointing it at stored_procedures/sharepoint
deploy-stored-procedures [--procs-dir D] [--server-role R]	recursively load the .sql files under --procs-dir, routing each file to the database named in its opening USE [<db>]; statement on that database's tier host for the chosen side — --server-role target (default: TARGET_SERVER / TARGET_REPORTING_SERVER) or --server-role source (SOURCE_SERVER / SOURCE_REPORTING_SERVER); files without a resolvable USE database are reported and skipped
run-deliverables [--procs-dir D] [--server-role R] [--ge]	walk the converted tree under --procs-dir for _deliverables/<proc>/ directories, execute each one's validation scripts (the 007_rollback_previous_procedure.sql stub is never run) against the database named by the scripts' opening USE [<db>]; header — falling back to 000_package_context.json — on that database's tier host for the chosen --server-role side, and write one Excel workbook <proc>.xlsx per proc directory capturing every result set, driver message, and error; --ge (or RUN_DELIVERABLES_GE=1) additionally runs the Great Expectations parity layer over each directory's _tables.json destination tables and appends the results as a final _expectations worksheet
compare-tables [--procs-dir D] [--filter-file F] [--roles R] [--ge] [--html-report F]	run the table-parity pytest suite (data_dp_ssis2sp/parity_suite, marker parity) comparing each _deliverables/<proc>/_tables.json manifest table between the SOURCE and TARGET servers; --ge adds the Great Expectations layer (needs the ge extra); returns pytest's exit code (an unreachable-server run all-skips and exits 0); ships in the wheel, runs from any directory (pip install data_dp_ssis2sp[parity]) — see Table parity
run-ge [--procs-dir D] [--out-dir O] [--roles R] [--filter-file F]	run only the Great Expectations parity layer (source vs target — no checksum layer) for each _deliverables/<proc>/_tables.json manifest table, grouped by database, and write one self-contained GX Data Docs HTML site per database under <out-dir>/<database>/ (default ge_reports/); both sides are tier-routed, an unroutable or unreachable database is reported and skipped (never fatal), and it exits 1 when any expectation failed else 0 — see Table parity
build-dag [--procs-dir D] [--master-name N] [--schema S] [--master-database DB]	build a cross-package dependency DAG over every proc in --procs-dir/_proc_manifest.json — edges from ExecutePackageTask references (000_package_context.json) and table lineage (writer of a destination table → reader of the same source/lookup_reference table, from _tables.json) — topologically ordered (declaration-order fallback on a cycle), and emit _dag.json, _dag.mmd (Mermaid), and <master-name>.sql: a master stored proc that EXECs every proc once by three-part [db].[schema].[proc] name in precedence order, each wrapped in TRY/CATCH that logs to dbo.MasterRunLog and THROWs so a failed step halts the run; no DB work
emit-email-proc [--out-dir D]	render the Database-Mail email notification stored proc (spEmailExample) from GlobalSettings (params/name from email_stored_procedure_spec, profile from email_mail_profile) into <out-dir>/spEmailExample.sql; no DB work
deploy-email-proc [--out-dir D]	render the email notification proc from settings (as emit-email-proc) and then load just that file onto the SQL Server (one-click generate + deploy)
emit-sharepoint-procs [--out-dir D]	render one OPENROWSET parquet-ingest load proc per SharePoint list (<schema>.spLoad_<entity>, reading <sp_target_folder>/<file>.parquet via the sp_external_data_source) under <out-dir>/sharepoint/; no DB work
finalise-adf [--adf-dir D] [--proc-out-dir D]	rewrite emitted SSIS Script activities to stored-proc calls, format inline SQL, and write the generated procs to --proc-out-dir; no DB work
emit-procs-from-register [--register F] [--out-dir D] [--param-map F]	read the SQL Dataset register CSV and emit one CREATE OR ALTER PROCEDURE per dataset under <out-dir>/reporting/; writes docs/parameter_type_map.csv for review; see SQL dataset register migration
emit-adf-from-register [--register F] [--out-dir D] [--max-activities N]	emit per-source ADF validation pipelines from the SQL Dataset register CSV; pipelines are written under <out-dir>/pipeline/; see SQL dataset register migration
emit-procs-from-reportserver [--register F] [--out-dir D] [--server H] [--database DB] [--schema S] [--metadata-typing]	read the live ReportServer.dbo.Catalog (Windows auth via cfg.sql_server), classify each register row's RDL dataset, and emit one CREATE OR ALTER PROCEDURE per SQL dataset under <out-dir>; writes a generation manifest CSV; non-SQL (DAX) datasets are skipped

Command reference

Every invocation has the same shape — two required global flags select the business + environment, an optional log-level override, then the subcommand and its operands:

data_dp_ssis2sp --business-name {awb,msb} --environment-name {dev,prod} \
                [--log-level {debug,info,warning,error}] \
                <command> [command options]

Global flags (parsed before the subcommand):

Flag	Required	Choices	Default	Effect
--business-name	yes	awb, msb	—	business prefix; seeds every derived setting via GlobalSettings
--environment-name	yes	dev, prod	—	deployment environment; seeds derived settings and the default log level
--log-level	no	debug, info, warning, error	derived from environment (dev→debug, prod→info, via LOG_VERBOSITY)	overrides console verbosity for this run; the interactive front-ends use it to scale captured output

Running the bare command with no subcommand prints help to stderr and exits 2. Operational parameters (servers, filters, linked-service names, …) are not CLI flags — they are computed by GlobalSettings from --business-name / --environment-name (see Configuration). Inspect what a given --business-name / --environment-name pair resolves to with verify-config.

Common exit codes: 0 success · 1 partial failure (some files failed / DB unreachable) · 2 config, parse, or I/O error · 3 emit-adf converted but a step's package is not in the SSIS catalog.

Conversion (offline, no DB)

convert <dtsx> [-o OUT]

Convert one .dtsx package into one consolidated, behaviour-equivalent T-SQL script (WITH … INSERT INTO … SELECT per destination).

The single-file convert emits a raw T-SQL batch and does not wrap the output in a stored procedure, so it never prepends the USE [<db>]; routing batch that convert-tree procs carry, even when the package resolves a database.

• Positional: dtsx — path to the .dtsx file.
• Options: -o, --output PATH — write the SQL here (resolved under BASE_EXPORT_FILE_PATH); omit for stdout.
• Consumes: CONVERT_INCLUDE_HEADER (emit the "Generated by" banner). CONVERT_PROCEDURE is hard-wired blank, so convert always emits a raw batch and never wraps the output in a stored procedure (see above).
• Writes: the SQL to stdout or -o; on -o, a wrote <path> line to stderr. A N warning(s) summary is printed to stderr unless LOG_VERBOSITY < 0.
• Exit: 0 on success; 2 on a parse/IO error.

inspect <dtsx>

Print the parsed component graph — connection managers, variables, Execute SQL tasks, and each Data Flow's components (with input/output column counts) and paths — then exit. Read-only diagnostic; writes nothing.

• Positional: dtsx — path to the .dtsx file.
• Exit: 0; 2 on a parse error.

inventory <path> [--format {table,csv,json}] [-o OUT]

List the distinct source and destination objects across one package or a whole tree — one row per endpoint answering "which data source, which server/database, which object, source or destination?". Read-only; parses the package(s) and reads the model, no transpilation and no DB.

• Positional: path — a .dtsx file, or a directory scanned recursively for .dtsx files.
• Options: --format {table,csv,json} (default table); -o, --output PATH — write the report here (resolved under BASE_EXPORT_FILE_PATH); omit for stdout.
• Columns: role (source/destination), data_source (the referenced connection manager's name), server and database (the host and target catalog resolved from the connection string, with the same @[$Project::X] parameter fallback the transpiler uses; empty if unresolved), object_name (the table read or written — OpenRowset/TableName; empty for a free SQL command or flat file). Rows are deduplicated across all packages and sorted for a stable diff.
• Writes: the report to stdout or -o; on -o, a wrote <path> line to stderr. A directory scan skips (and reports on stderr) any package that fails to parse; a single named file that fails to parse is a hard error.
• Exit: 0; 2 on a parse error of a single named file.

$ data_dp_ssis2sp --business-name awb --environment-name dev inventory examples/sales_etl.dtsx
ROLE         DATA SOURCE  SERVER     DATABASE  OBJECT NAME
destination                                    dbo.RegionSummary
destination  LocalDW      localhost  SalesDW   [dbo].[FactSalesEnriched]
source
source       LocalDW      localhost  SalesDW

convert-tree <input> <output>

Recursively convert a directory of .dtsx files into a mirrored .sql tree, plus a collapsed main orchestrator proc and a _proc_manifest.json mapping each package to its procedure name (consumed downstream by extract-agent-jobs).

Each connection-bearing proc opens with a USE [<db>]; batch followed by a CREATE SCHEMA guard, routing it to the database resolved from the package's primary connection. Connection-less procs (orchestrators, or packages with no Data Flow Task) resolve no database, so they carry no USE prefix.

• Positional: input — directory scanned recursively for .dtsx; output — directory the mirrored .sql tree is written into (resolved under BASE_EXPORT_FILE_PATH).
• Consumes: CONVERT_INCLUDE_HEADER, CONVERT_NO_ORCHESTRATOR (suppress the collapsed main proc), CONVERT_QUALIFY_FROM_CONNECTION (qualify objects with the connection's database), BLOB_STORAGE_PATH.
• Writes: the .sql tree + _proc_manifest.json. When BLOB_STORAGE_PATH is set, uploads the finished tree to Blob Storage (via DefaultAzureCredential) as the final step and prints each published <loc>.
• Output: a converted <src> -> <dst> / failed <src>: <err> line per file, then converted N, failed M.
• Exit: 0 when every file converts; 1 if any file failed; 2 on a fatal IO error.

--environment-file / --overrides-file layer optional JSON bindings over the design-time parameter defaults; precedence is design → manifest → environment → override (override highest). Literal-wins caveat: a token-free literal in a .dtsx connection string (e.g. a hard-coded Initial Catalog) beats every binding layer. Seed a starter file with emit-binding-template --project <dir> --out <file>. See docs/binding-files.md for the JSON schema, precedence chain, and the literal-wins caveat in full.

Extraction (Windows-auth SQL Server, on-prem)

These reach a domain SQL Server over ODBC with Windows Integrated auth — run them on a self-hosted Windows agent whose service account has read access; they require SOURCE_SQL_SERVER and exit 2 if it is blank.

extract-packages

Connect to a SQL Server package store and write every stored SSIS package to disk as .dtsx. PACKAGE_STORE=auto probes DB_ID('SSISDB') and prefers the catalog, falling back to the legacy msdb store.

Extraction is scoped by GlobalSettings.DEFAULT_PACKAGE_EXTRACT_SCOPE — a catalog folder → allowed-projects map (empty project list = every project in that folder; folder absent = skipped; empty map = extract everything). Matching is exact and case-insensitive, out-of-scope projects are dropped before their .ispac is downloaded, and scope entries matching nothing in the catalog are reported in _packages_warnings.log. PACKAGE_FILTER composes with the scope as a package-name substring filter. The msdb store has no project tier, so only the scope's folder keys apply there.

• Consumes: SOURCE_SQL_SERVER (+ MSSQL_* connection vars), PACKAGE_STORE (auto/msdb/ssisdb), PACKAGE_FILTER, GlobalSettings.package_extract_scope (folder/project allowlist), PACKAGE_CLEAN (wipe the output dir first), PACKAGE_EXPANDED (also write @Project.manifest/.params/.conmgr).
• Writes: the .dtsx tree under <base>/packages/, plus _packages_manifest.json and (on skips) _packages_warnings.log. Prints a wrote <path> line per package and an extracted N package(s) summary.
• Exit: 0; 2 on a config or IO error.

extract-ispacs

Download every .ispac project archive from the source SSISDB catalog and write each one verbatim (no unzipping) as <folder>/<project>.ispac. .ispac archives exist only in the catalog, so a server without SSISDB exits 2 rather than silently writing nothing. Extraction is scoped by the same GlobalSettings.package_extract_scope folder/project allowlist as extract-packages; pass --get-all to ignore the allowlist and download every project in the catalog.

• Consumes: SOURCE_SQL_SERVER (+ MSSQL_* connection vars), GlobalSettings.package_extract_scope (folder/project allowlist; bypassed by --get-all), PACKAGE_CLEAN (wipe the output dir first).
• Writes: the .ispac tree under <base>/ispacs/, plus _ispacs_manifest.json and (on skips) _ispacs_warnings.log. Prints a wrote <path> line per project and an extracted N .ispac project(s) summary.
• Exit: 0; 2 on a config or IO error, or when the server has no SSISDB.

extract-agent-jobs

Read msdb.dbo.sysjobs* over ODBC and emit one sequence-preserving JSON file per SQL Server Agent job (input to emit-adf).

• Consumes: SOURCE_SQL_SERVER, AGENT_JOB_FILTER, AGENT_CATEGORY_FILTER (default Data Warehouse — silently skips other categories; set blank for all), PROC_MANIFEST_PATH (when set, rewrites subsystem=SSIS steps to EXEC <proc>; using _proc_manifest.json).
• Writes: one JSON per job under <base>/jobs/; prints a wrote <path> line each.
• Exit: 0; 2 on a config/IO error or an invalid/unsupported manifest (manifest invalid: … / manifest version unsupported: … on stderr).

pipeline

Run the full extraction chain end to end (mirrors run.bat pipeline), stopping at the first failing stage and returning its exit code:

1. extract-packages → <base>/packages/
2. convert-tree → <base>/stored_procedures/ (+ _proc_manifest.json)
3. extract-agent-jobs → <base>/jobs/, with the step-2 manifest wired in so SSIS steps are rewritten to EXEC calls.

• Consumes: the union of the three stages' settings.
• Exit: 0 when all stages pass; otherwise the first failing stage's code.

ADF artifact emission (offline, no DB)

emit-adf [--jobs-dir D] [--out-dir D]

Convert the per-job JSON from extract-agent-jobs into ADF Git-format pipeline + trigger JSON. SSIS steps become catalog Script activities (create_execution); TSQL steps reference <SOURCE_LINKED_SERVICE_PREFIX><database>. Adds the per-step logging + notify layer, auto-injects any @Project.manifest execution parameters, and surfaces resolved connections as pipeline parameters. This is the richest command — see ADF linked-service resolution, Execution-parameter injection, and Connection qualification.

• Options: --jobs-dir D (default jobs) — per-job JSON input; --out-dir D (default stored_procedures) — ADF Git-format output root.
• Consumes (all required, blank ⇒ exit 2): ADF_SSISDB_LINKED_SERVICE, BLOB_STORAGE_LINKED_SERVICE, BLOB_CONTAINER_NAME, ADF_NOTIFY_FUNCTION_LINKED_SERVICE, ADF_NOTIFY_FUNCTION_NAME; plus SOURCE_LINKED_SERVICE_PREFIX, ADF_TRIGGER_TIMEZONE, ADF_JOB_PREFIXES (filter), ADF_NOTIFY_MECHANISM (stored_proc default / azure_function), ADF_EMAIL_STORED_PROCEDURE. Setting ADF_INTEGRATION_RUNTIME (with SOURCE_SQL_SERVER) also emits publishable linkedService/*.json and then additionally requires LINKED_SERVICE_SECRET_NAME + ADF_KEYVAULT_LINKED_SERVICE.
• Writes: <out-dir>/pipeline/*.json, trigger/*.json, optionally linkedService/*.json, and _adf_param_report.json when a manifest is found.
• Exit: 0 clean · 2 missing config / param-report FAIL / error (dispatched outside the global error handler so the code is never flattened) · 3 converted but ≥1 step's package is not in the catalog (loud stderr banners
  • Fail placeholder activities; FAIL takes precedence over 3).

generate-linked-services [--out-dir D]

Generate ADF linked-service JSON (Key Vault, SQL, storage, SharePoint) from GlobalSettings — the standalone counterpart to emit-adf's inline emission.

• Options: --out-dir D — output dir for linkedService/*.json (default: the configured linked_service_output_dir).
• Writes: one linkedService/<name>.json per generated document; prints a Generated N … summary and each path.
• Exit: 0; 2 on an IO/value error.

emit-adf-sharepoint --out-dir D

Emit ADF Git-format artifacts to migrate on-prem SharePoint Server lists → ADLS Gen2 (parquet), one pipeline per subsite, driven entirely by GlobalSettings (sharepoint_migration_targets). One-shot migrate — no trigger is generated. See SharePoint list migration.

• Options: --out-dir D — required; one subdir per subsite is written under it (linkedService/, dataset/, pipeline/).
• Output: a per-subsite wrote N file(s) summary line.
• Exit: 0; 2 on any IO/config error (never raises).

finalise-adf [--adf-dir D] [--proc-out-dir D]

Post-process emitted pipelines: rewrite SSIS Script activities into stored-proc calls, format the inline SQL, and publish the generated procs. No DB work.

• Options: --adf-dir D (default adf) — ADF Git-format root containing pipeline/*.json; --proc-out-dir D (default: configured ssis_proc_output_dir) — output dir for generated procs.
• Consumes: sql_format_tool_path, sql_format_style_path (formatter).
• Output: processed N pipeline(s), wrote M proc(s) to <dir>.
• Exit: 0; 2 on a config/IO/conversion error.

Stored-procedure rendering & loading

load-stored-procedures [--procs-dir D]

Connect to the target SQL Server and execute every .sql at --procs-dir. Touches the database. Understands two modes:

• Per-database tree (produced by emit-procs-from-register): when --procs-dir contains immediate child directories named after known databases (deployment_databases ∪ reporting_databases), the loader opens one connection per database directory, runs a CREATE SCHEMA [reporting] guard, then executes every .sql under that subtree (sorted — 00_types/ deploys before reporting/). Hard-fail: if _unrouted/ exists and contains any .sql file, the command prints an error to stderr mentioning _unrouted, executes no batches, and returns exit code 1. Fix database routing in the register first.

• Legacy / single-file (e.g. deploy-email-proc): when --procs-dir is a single .sql file, or a directory with no database-named subdirectories, the loader uses the existing behaviour — connects to sp_migration_target_database and guards migration_sql_schema.

• Options: --procs-dir D (default stored_procedures) — a single .sql file, a flat directory, or a per-database tree produced by emit-procs-from-register.

• Consumes: SOURCE_SQL_SERVER, migration_sql_schema, sp_migration_target_database (legacy path only).

• Exit: 0 on success; 1 if a database is unreachable or _unrouted/ contains unresolved procs; 2 on a config error.

deploy-stored-procedures [--procs-dir D] [--server-role R]

Recursively load every .sql file under --procs-dir, routing each file by its own USE statement. Touches the database. The target database is read from the file's opening USE [<db>]; batch (the form emit-procs-from-register writes); the file is then loaded onto that database on its tier's host for the chosen --server-role side — the default target uses TARGET_SERVER (deployment-list databases) and TARGET_REPORTING_SERVER (reporting-list databases); source uses SOURCE_SERVER and SOURCE_REPORTING_SERVER instead. Each cleanly-loaded file prints one deploy-stored-procedures: loaded <file> -> [<db>].[<schema>] line as it commits.

Files are grouped by their USE database so each database gets a single connection; within a group sorted path order keeps a 00_types/ table-type asset ahead of the procs that reference it, and a CREATE SCHEMA guard runs once per database before its files. Each .sql file executes in its own transaction and is rolled back on failure, so the run continues past a bad file. A file that has no USE statement (such as a connection-less convert-tree proc), or whose database is in neither deployment_databases nor reporting_databases, is reported to stderr and skipped — the remaining files still load.

Use this (rather than load-stored-procedures) when the proc files carry their own USE [<db>]; routing and you want them deployed to the per-tier TARGET servers without arranging them into a per-database directory tree.

• Options: --procs-dir D (default stored_procedures) — a single .sql file or a directory of .sql files (recursed); each file must open with a USE [<db>]; batch naming its target database. --server-role R (default target) — which side's hosts to load onto: target or source.
• Consumes: TARGET_SERVER, TARGET_REPORTING_SERVER (or SOURCE_SERVER, SOURCE_REPORTING_SERVER with --server-role source), deployment_databases, reporting_databases (tier routing); report_server_schema (the per-database CREATE SCHEMA guard).
• Exit: 0 when every file loads; 1 if any file is unroutable / has no USE / fails to load, or a database is unreachable; 2 on a config error (blank resolved host).

run-deliverables [--procs-dir D] [--server-role R] [--ge]

Execute the DBA validation deliverables that convert-tree writes beside every emitted proc (_deliverables/<proc>/*.sql) and capture their output to Excel. Touches the database. Every _deliverables proc directory under --procs-dir is discovered recursively; its scripts run in name order — the 007_rollback_previous_procedure.sql stub is never executed — and one workbook <proc>.xlsx is written into the proc directory with one worksheet per script holding that script's result sets, driver messages (PRINT / SET STATISTICS IO text), and any error.

Each proc directory is routed to a database, first match wins: the opening USE [<db>]; header of its first runnable script, else the first non-empty connections[].database in its 000_package_context.json (trees generated before the deliverables carried a USE header). The database's tier host is resolved exactly as deploy-stored-procedures does for the chosen --server-role side, and one connection is opened per distinct routed database. A proc directory that resolves no database, or whose database is unroutable or unreachable, is reported to stderr and skipped (no workbook) — the remaining directories still run and the command exits non-zero. Each script runs in its own transaction (rolled back on failure) and a failing script still gets its worksheet with an ERROR: row. The Great Expectations hook (run_expectations) runs once per proc directory between execution and the workbook write — a no-op by default; with --ge (or RUN_DELIVERABLES_GE=1) it runs the Great Expectations parity layer over each destination table in the directory's _tables.json manifest — source vs target, both sides tier-routed regardless of --server-role — and the per-expectation results land in a final _expectations worksheet. A failing GX layer (an unroutable manifest database, a missing ge extra, a GX error) is reported to stderr and forces a non-zero exit without blocking the workbook. The run ends with a run-deliverables: executed <n>/<total> script(s), <f> failed, wrote <w> workbook(s) summary line.

• Options: --procs-dir D (default stored_procedures) — root of the converted stored-procedures tree, scanned recursively for _deliverables directories. --server-role R (default target) — which side's hosts to run against: target or source. --ge — run the Great Expectations parity layer per proc directory (requires the ge extra; RUN_DELIVERABLES_GE=1 enables it without the flag).
• Consumes: TARGET_SERVER, TARGET_REPORTING_SERVER (or SOURCE_SERVER, SOURCE_REPORTING_SERVER with --server-role source), deployment_databases, reporting_databases (tier routing; with --ge both sides' hosts are consumed for the parity comparison), RUN_DELIVERABLES_GE.
• Exit: 0 when every script runs cleanly; 1 if any script fails, a proc directory / database is skipped, or the --ge expectations hook fails; 2 on a config error (blank resolved host).

emit-email-proc [--out-dir D]

Render the Database-Mail email notification stored proc (spEmailExample) from GlobalSettings (params/name from email_stored_procedure_spec, profile from email_mail_profile). No DB work.

• Options: --out-dir D (default stored_procedures) — written as <out-dir>/<proc-name>.sql.
• Exit: 0.

deploy-email-proc [--out-dir D]

emit-email-proc + immediately load that one file onto the SQL Server (one-click generate + deploy). Touches the database.

• Options: --out-dir D (default stored_procedures) — render location before loading.
• Exit: 0 on success; 1 if the database is unreachable; 2 on a config error.

emit-sharepoint-procs [--out-dir D]

Render one OPENROWSET parquet-ingest load proc per SharePoint list (<schema>.spLoad_<entity>, reading <sp_target_folder>/<file>.parquet via the sp_external_data_source). No DB work.

• Options: --out-dir D (default stored_procedures) — procs are written under <out-dir>/sharepoint/.
• Output: wrote N procs to <dir>.
• Exit: 0.

SQL dataset register migration

See SQL dataset register migration for the full workflow (the register CSV is read as cp1252, header on row 4).

emit-procs-from-register [--register F] [--report-params F] [--out-dir D] [--param-map F]

Read the SQL Dataset register CSV and emit one CREATE OR ALTER PROCEDURE per dataset under <out-dir>/reporting/, typing parameters via the SSRS export and a reviewable parameter-type map. A multi-value parameter used as IN (@p) is emitted as a scalar NVARCHAR(MAX) and split in-proc with STRING_SPLIT, so the procs are self-contained (no table type to pre-deploy). No DB work.

• Options: --register F (default docs/SQL Dataset Text Register(SQL Dataset Conversion).csv); --report-params F (default docs/Report Server (Report Parameters).csv) — SSRS export for parameter typing; --out-dir D (default stored_procedures); --param-map F (default docs/parameter_type_map.csv) — written/refreshed for review.
• Output: emitted N procs to <dir>/reporting, plus a flagged for manual review: line listing dataset IDs that need attention.
• Exit: 0.

emit-adf-from-register [--register F] [--report-params F] [--out-dir D] [--max-activities N]

Read the same register CSV and emit one per-source ADF validation pipeline (SqlServerStoredProcedure activity per proc) under <out-dir>/pipeline/, chunked to stay under ADF's per-pipeline activity ceiling. No DB work.

• Options: --register F / --report-params F (defaults as above); --out-dir D (default adf); --max-activities N (default 40) — max activities per pipeline file before chunking into validate_<src>_2.json, ….
• Output: emitted N pipelines across M sources to <dir>/pipeline.
• Exit: 0.

Diagnostics (offline, no DB)

verify-config

Print every resolved derived value (one key: value per line) for the chosen --business-name / --environment-name and exit. Use this to confirm linked services, filters, and paths before a real run.

• Exit: 0.

show-settings

Print the resolved settings as a formatted rich table and exit.

• Exit: 0.

Configuration

Every operational parameter is computed by data_dp_global_settings.GlobalSettings from the two required CLI flags — --business-name (awb/msb) and --environment-name (dev/prod). There is no config.yaml and no per-field environment-variable override: GlobalSettings.settings_customise_sources returns only the constructor inputs, so pydantic's env / dotenv / secrets sources are disabled for the settings fields. Inspect the fully resolved values with data_dp_ssis2sp show-settings (rich table) or data_dp_ssis2sp verify-config.

There are no secrets in source: SQL Server access uses Windows Integrated auth (Trusted_Connection=yes) for the on-prem source/extraction servers and Azure AD (DefaultAzureCredential) for the Azure SQL validation target.

.env — the only external inputs

Six infrastructure endpoints are read from the process environment (loaded from a .env at the repo root via python-dotenv when GlobalSettings is imported). They are the only values not derived from the two flags, and all six must be set or import fails:

# .env — required infrastructure endpoints (no secrets)
SOURCE_SERVER=sql-host.example.com               # deployment source SQL Server
TARGET_SERVER=sql-host.example.com               # deployment target SQL Server
SOURCE_REPORTING_SERVER=rpt-host.example.com      # reporting source SQL Server
TARGET_REPORTING_SERVER=rpt-host.example.com      # reporting target SQL Server
DEV_AZURE_SUBSCRIPTION_ID=00000000-0000-0000-0000-000000000000
PROD_AZURE_SUBSCRIPTION_ID=00000000-0000-0000-0000-000000000000

Everything else — linked-service names, the BLOB_STORAGE_LINKED_SERVICE / BLOB_CONTAINER_NAME log targets, the integration-runtime name, schema, timezone, the LINKED_SERVICE_SECRET_NAME / LINKED_SERVICE_USER_NAME credential settings, and so on — is a computed_field derived from --business-name / --environment-name. The table below is a reference of the resolved settings (surfaced via to_runtime_env()), not a list of variables you set by hand. The differential-validation harness is the one exception: it reads TARGET_SQL_SERVER, MSSQL_SOURCE_DSN, and MSSQL_TARGET_DSN straight from the environment, outside GlobalSettings.

Values shown are what dev/awb resolves to (show-settings). None of these are set by hand — they are computed_fields derived from the two flags (plus the .env server/subscription endpoints above).

Setting	Value (dev/awb)	Purpose
Identity & servers
ENVIRONMENT_NAME	dev	the --environment-name flag; seeds every derived value + the default log level
BUSINESS_NAME	awb	the --business-name flag; prefixes names and local paths
SOURCE_SQL_SERVER	sqldwh01.example.com	deployment source host (.env SOURCE_SERVER); extraction + TSQL linked services
TARGET_SQL_SERVER	sqldwh02.example.com	deployment target host (.env TARGET_SERVER)
AZURE_SUBSCRIPTION_ID	00000000-…	per-env subscription (.env DEV/PROD_AZURE_SUBSCRIPTION_ID)
RESOURCE_GROUP	rg-data-<env>-<region>-<team>	ADF resource group
DATA_FACTORY_NAME	df-data-<env>-<region>-<team>-datafactory	ADF / service-account name
Linked services & Key Vault
KEY_VAULT_LINKED_SERVICE	awb_dev_keyvault	KV linked-service name referenced by every secret
KEYVAULT_BASE_URL	https://kv-data-<env>-<region>-<team>-key.vault.azure.net/	KV base URL
SOURCE_LINKED_SERVICE_PREFIX	awb_dev_src_	prefix for per-db source linked services
TARGET_LINKED_SERVICE_PREFIX	awb_dev_target_	prefix for per-db target linked services
ADF_SSISDB_LINKED_SERVICE	awb_dev_instance_LS	LS for SSISDB catalog Script activities
BLOB_STORAGE_LINKED_SERVICE	awb_dev_src_sadata<env>transient	blob LS Script activities log through
ADF_NOTIFY_FUNCTION_LINKED_SERVICE	awb_dev_notify_azure_function_LS	Azure Function LS (azure_function mechanism)
INTEGRATION_RUNTIME / ADF_INTEGRATION_RUNTIME	SelfHostedIntegrationRuntime	self-hosted IR name
LINKED_SERVICE_SECRET_NAME	svc-account	KV secret name for the service-account password (every generated LS)
LINKED_SERVICE_USER_NAME	DOMAIN\svc-account	Windows user name on generated SQL + SharePoint LS (not in to_runtime_env)
Blob logging & data lake
BLOB_STORAGE_ACCOUNT_NAME	sadata<env>transient	transient log storage account
BLOB_CONTAINER_NAME	awb-dev-logs	container; Script activities log to <blob_container_name>/<pipeline>/<runId>
BLOB_STORAGE_PATH	https://…/awb-dev-logs	full blob URL
ADF_STORAGE_PATH	https://sadata<env>datalake.dfs.core.windows.net/	ADLS Gen2 data-lake endpoint
Notify & email
ADF_NOTIFY_MECHANISM	stored_proc	stored_proc (default) or azure_function
ADF_EMAIL_STORED_PROCEDURE	DW_PRESENTATION.DataFactory.spEmailExample	proc each notify activity calls (stored_proc mechanism)
ADF_NOTIFY_FUNCTION_NAME	send_email	function invoked (azure_function mechanism)
PRIMARY_EMAIL	[e-mail]	primary notification address
EMAIL_RECIPIENTS	[e-mail]	recipient list
Extraction & conversion
DATA_SOURCE_LIST	SSISDB	databases SQL linked-service pairs are generated for
PACKAGE_STORE	auto	auto | msdb | ssisdb
PACKAGE_FILTER	(blank)	package name filter; blank = all
GlobalSettings.package_extract_scope	Lendfast, SHAREPOINT_ETL, Standard_Imports, Ultracs_ETL (see DEFAULT_PACKAGE_EXTRACT_SCOPE)	catalog folder → allowed-projects allowlist for extract-packages; settings-only (not an env var)
PACKAGE_CLEAN	false	wipe the output dir before writing
PACKAGE_EXPANDED	true	also write the project files
AGENT_JOB_FILTER	(blank)	job name filter; blank = all
AGENT_CATEGORY_FILTER	Data Warehouse	Agent-job category filter (case-insensitive exact)
ADF_JOB_PREFIXES	ETL,Maintence,Reporting,Integration	job-name prefix filter for emit-adf; non-matching jobs skipped
PROC_MANIFEST_PATH	stored_procedures/_proc_manifest.json	rewrites SSIS steps to call converted procs
CONVERT_INCLUDE_HEADER	true	emit the "Generated by" banner
CONVERT_NO_ORCHESTRATOR	false	suppress the collapsed main orchestrator proc
CONVERT_QUALIFY_FROM_CONNECTION	true	qualify objects with the connection's database ([db].[schema].[object])
LOG_VERBOSITY	2 (dev) / 1 (prod)	0=WARNING 1=INFO 2=DEBUG
SQL connection
MSSQL_SERVER_PORT	1433	TCP port
MSSQL_DRIVER	ODBC Driver 18 for SQL Server	ODBC driver name
MSSQL_TRUST_CERT	true	TrustServerCertificate (encryption is always on)
MSSQL_DATABASE	(blank)	connection database; blank ⇒ master (queries are 3-part qualified)
SharePoint
SOURCE_SHAREPOINT_URL	http://sharepoint.example/_api/	on-prem SharePoint OData root
SHAREPOINT_SOURCE_URLS	http://sharepoint.example/<SubSite>, …	per-subsite OData URLs
TARGET_SHAREPOINT_URL	http://PENDING_dev	SharePoint Online target (PENDING)
SHAREPOINT_USER_NAME	svc-account	on-prem SharePoint account
MIGRATION_SQL_SCHEMA	DataFactory	schema for the migration/load procs
SP_MIGRATION_TARGET_DATABASE	DW_PRESENTATION	database the load procs target
ADF_TRIGGER_TIMEZONE	E. Australia Standard Time	timezone written into generated Schedule triggers

The differential-validation harness additionally reads TARGET_SQL_SERVER, MSSQL_SOURCE_DSN, and MSSQL_TARGET_DSN from the environment (outside GlobalSettings). Output directories (LINKED_SERVICE_OUTPUT_DIR, PACKAGES_OUT_DIR, JOBS_OUT_DIR, ADF_OUT_DIR, …) are also computed, all rooted at BASE_EXPORT_FILE_PATH (the current working directory).

Per-step logging + notification layer — deploy prerequisites. This layer is mandatory: emit-adf requires ADF_SSISDB_LINKED_SERVICE, BLOB_STORAGE_LINKED_SERVICE, BLOB_CONTAINER_NAME, ADF_NOTIFY_FUNCTION_LINKED_SERVICE, and ADF_NOTIFY_FUNCTION_NAME. When ADF_INTEGRATION_RUNTIME (and SOURCE_SQL_SERVER) are also set, LINKED_SERVICE_SECRET_NAME and ADF_KEYVAULT_LINKED_SERVICE become required too — emit-adf always emits the Windows-auth shape (authenticationType: "Windows", password from Key Vault, no connectionString); the userName in every generated SqlServer linked service is the data_factory_name account (e.g. df-data-<env>-<region>-<team>-datafactory), and LINKED_SERVICE_SECRET_NAME must name the Key Vault secret holding its password. If any required field is blank it exits 2, listing every missing one — it never silently skips. Every script activity in every pipeline gains, on its own dependency edges:

• Notify_<step>_Failure — an Azure Function Activity that calls the notify function linked service (ADF_NOTIFY_FUNCTION_LINKED_SERVICE) on that script's failure, invoking ADF_NOTIFY_FUNCTION_NAME. The function key is never embedded in generated JSON — it is resolved at runtime from Key Vault by the linked service. Each Script activity also carries a native logSettings block that routes execution logs to the blob-storage linked service ({source_linked_service_prefix}{blob_storage_account_name}) under <blob_container_name>/<pipeline>/<runId>.
• Raise_<step>_Failure — a Fail activity that re-raises after the notify so a failed pipeline still ends red. Emitted only for steps that do not already route on failure and do not quit reporting success (those handle it themselves; on_fail_action=1 ends the pipeline green).

Per-step success is a log write only (no notify). One end-of-process notification is emitted at the job level: Notify_<job>_Success — an Azure Function Activity depending on the job's terminal step(s) succeeding, calling the same notify function with status: "Succeeded" (the function branches on status). So a run emails once on overall success, and per failed step on failure.

Before publishing: (1) provision the Azure Function linked service and the Key Vault secret holding its function key, and grant ADF access to the vault (when ADF_NOTIFY_FUNCTION_APP_URL + ADF_NOTIFY_FUNCTION_KEY_SECRET + ADF_KEYVAULT_LINKED_SERVICE are set, emit-adf generates linkedService/<name>.json for the function; otherwise it is referenced by name and DevOps creates it in the factory); (2) ensure the blob-storage linked service (BLOB_STORAGE_LINKED_SERVICE) exists in the factory with write access to the blob container (BLOB_CONTAINER_NAME) — its auth (MSI / Key Vault) lives in the factory, not in generated JSON. Script-activity logs land at <blob_container_name>/<pipeline>/<runId>. The generated logging pipelines have not been validated against a live ADF factory by this build — validate in a dev factory before production.

Interim email notification — SQL Server stored procedure

While the Azure Function infrastructure is being provisioned, emit-adf defaults to a SQL Server stored-procedure notify mechanism. Instead of an AzureFunctionActivity, each Notify_<step>_Failure and Notify_<job>_Success activity is emitted as a SqlServerStoredProcedure activity that calls a configurable stored procedure.

These settings are computed by GlobalSettings from --business-name / --environment-name and surfaced through to_runtime_env(); the values below are what emit-adf resolves for dev/awb. (The "" you may see on the AdfConvertConfig dataclass fields is only an internal fallback — the CLI always populates these from GlobalSettings.)

Setting	Value (dev/awb)	Purpose
ADF_NOTIFY_MECHANISM	stored_proc	Notification back-end: stored_proc (default) or azure_function (legacy fallback).
ADF_EMAIL_STORED_PROCEDURE	DW_PRESENTATION.DataFactory.spEmailExample	Fully-qualified procedure name called by every notify activity (stored_proc mechanism).

The stored procedure is called over the same linked service as the SSISDB catalog (ADF_SSISDB_LINKED_SERVICE). There is no separate email linked-service setting; the SSISDB linked service is reused.

Set ADF_NOTIFY_MECHANISM=azure_function to restore the Azure Function path. The Azure Function activity builders are retained in the converter and become active again when this variable is set — no code change is required at provisioning time, only an environment-variable flip.

Azure Function payload — how parameters reach the endpoint. Parameters are sent in the HTTP POST body as JSON, not as ADF pipeline parameters. The function is selected by functionName on the linked service the activity references (function key resolved from Key Vault at runtime). The generated Notify_<step>_Failure activity is:

{
  "name": "Notify_<step>_Failure",
  "type": "AzureFunctionActivity",
  "dependsOn": [
    { "activity": "<step>", "dependencyConditions": ["Failed"] }
  ],
  "linkedServiceName": {
    "referenceName": "ls_notify_function",
    "type": "LinkedServiceReference"
  },
  "typeProperties": {
    "functionName": "NotifyOnFailure",
    "method": "POST",
    "headers": { "Content-Type": "application/json" },
    "body": {
      "jobName": "<step>",
      "status": "Failed",
      "pipelineRunId": "@pipeline().RunId",
      "errorMessage": "@activity('<step>').Error.Message"
    }
  }
}

The body mixes two kinds of value:

field	source	resolved
jobName	baked literal (step name, build time)	static string
status	baked literal "Failed"	static string
pipelineRunId	@pipeline().RunId	ADF system variable, runtime
errorMessage	@activity('<step>').Error.Message	failed step's error text, runtime

At run time the Azure Function Activity fires on the step's Failed edge, ADF evaluates the @… expressions in body (substituting the real run id + error message), leaves the literals as-is, and POSTs the resulting JSON to the function (resolved via the linked service, function key from Key Vault) with Content-Type: application/json. The function reads the fields from the request body (req.body.jobName, req.body.errorMessage, …). This relies on ADF evaluating @-prefixed strings inside a body object's values; a literal @ must be escaped as @@.

Validation DSN precedence. For each role (source / target), the explicit DSN variable wins if set; otherwise it is derived from the hostname variable — source as Windows-auth (honours MSSQL_DRIVER / MSSQL_SERVER_PORT / MSSQL_DATABASE / MSSQL_TRUST_CERT), target with Authentication=ActiveDirectoryDefault (Azure SQL has no Windows auth). If neither is set, the command exits with SqlServerUnavailable.

Example:

export SOURCE_SQL_SERVER=your-sql-server   # required for extraction; Windows auth, no password
export BASE_EXPORT_FILE_PATH=/build/artifacts/2025-001
export LOG_VERBOSITY=1

uv run data_dp_ssis2sp extract-packages              # → …/packages
uv run data_dp_ssis2sp convert-tree packages sql     # → …/sql

ADF linked-service resolution (emit-adf)

How activity linkedServiceName references are built (values are computed by GlobalSettings):

1. SSIS steps (catalog Script activities): every generated SSISDB.catalog.create_execution Script activity references ADF_SSISDB_LINKED_SERVICE verbatim as its linkedServiceName. The value is required — emit-adf exits 2 if it is empty (run.bat emit-adf guards this too). Point it at the instance-level linked service for the SQL Server that hosts the SSISDB catalog (e.g. ls_my_instance), running over the self-hosted integration runtime.
2. TSQL steps: the reference is <SOURCE_LINKED_SERVICE_PREFIX><database_name> with the database segment lowercased to match the factory convention — prefix src_ + step database MY_DB → src_my_db. Optional; defaults to the lowercased bare database name when the prefix is unset. A TSQL step with no database and no prefix falls back to ADF_SSISDB_LINKED_SERVICE (an empty linked-service reference fails ADF publish validation).

Generating the linked services (publishable output): set ADF_INTEGRATION_RUNTIME to the self-hosted IR name (and have SOURCE_SQL_SERVER set) and emit-adf writes <out_dir>/linkedService/<name>.json for every linked service it referenced, connectVia the named IR. Auth is always Windows (the factory's governed pattern); there is no SQL-auth or Integrated-Security shape. When ADF_INTEGRATION_RUNTIME is set you MUST also provide the Key Vault credential — LINKED_SERVICE_SECRET_NAME and ADF_KEYVAULT_LINKED_SERVICE — or emit-adf fails fast (AdfConfigError listing the missing vars) rather than emitting an insecure document. Each definition uses the modern SqlServer typeProperties: server, database (original case), encrypt: mandatory, trustServerCertificate (from MSSQL_TRUST_CERT), authenticationType: Windows, a userName of the data factory service account (data_factory_name, e.g. df-data-<env>-<region>-<team>-datafactory), and an AzureKeyVaultSecret password reference. LINKED_SERVICE_SECRET_NAME must store the password for that data_factory_name account. Presenting an explicit Windows credential (rather than the IR host's integrated-security identity) is what lets a Script activity's connection delegate past the first hop without an ANONYMOUS LOGON failure.

The output directory (pipeline/ + trigger/ + linkedService/) then matches the ADF Git layout and can be copied into the factory repo and published directly. Leave ADF_INTEGRATION_RUNTIME unset when the calling repo already generates its own linked services — pipelines then emit by-name references into the existing set.

ADF naming: pipeline/trigger/activity names collapse every run of non-alphanumeric characters into a single _ (Run My Job\Main → Run_My_Job_Main); activity names are capped at ADF's 55-character limit and deduplicated with a _<step_id> suffix on collision.

Check what will be used before a run with uv run data_dp_ssis2sp verify-config.

Execution-parameter injection (@Project.manifest auto-discovery)

emit-adf automatically discovers every @Project.manifest under the extracted-packages directory (<base>/packages/**/@Project.manifest) and injects the SSIS project's execution parameters into every catalog Script activity it emits — no flag required. Parameters from all discovered manifests are merged (exact duplicates dropped):

uv run data_dp_ssis2sp --business-name awb --environment-name dev \
    emit-adf --jobs-dir jobs --out-dir adf

What is injected. Non-sensitive, non-SHIR-owned parameters (Booleans, Int32s, portable string scalars such as InitialCatalog, TargetServerVersion) are rendered as EXEC SSISDB.catalog.set_execution_parameter_value statements placed after the SYNCHRONIZED line and before catalog.start_execution in each catalog Script activity's T-SQL.

Connection/credential params are not injected (SHIR-owned). Parameters whose property suffix is ConnectionString, Password, Username, ServerURL, etc. are classified as SHIR-owned (the self-hosted integration runtime already manages those via ADF linked services) and appear only in the validation report.

Environment runtime token. When an injected value contains the env token (default "dev", matches --environment-name), the T-SQL value is emitted as a concatenation referencing the ADF script variable @environment:

@parameter_value = N'DW_' + @environment + N'_EXTRACT'

The environment script variable is added to scripts[0].parameters and resolves to 'prod' when the pipeline runs in a Data Factory whose name contains -prod-, and 'dev' otherwise — matching the df-data-{env}-* naming convention.

Validation report. _adf_param_report.json is written to --out-dir. It contains a verdict (PASS/FAIL) and seven buckets:

Bucket	Content	Verdict impact
matched_injected	Injected params applied to ≥1 activity	—
supplied_via_shir	Sensitive + SHIR-owned params (no values exposed)	—
required_missing	Required param with no usable value	FAIL
in_config_unused	Injectable param whose scope matched no activity	warning
type_issues	Param with an unrecognised DataType code	FAIL
ambiguous	Package-scoped param name present in multiple packages	FAIL
uncovered_connections	SHIR-owned connection with no matching generated linked service	warning

emit-adf exits 2 when verdict == "FAIL" (same as missing-config), with the blocking parameter names printed to stderr. A clean PASS run exits 0 (or 3 if any step is not-in-catalog; FAIL takes precedence over 3).

When no @Project.manifest is found under the packages dir (e.g. an msdb-only export with no project tier), emit-adf produces byte-identical output to the pre-injection behaviour and writes no report.

Connection-derived pipeline parameters. When a catalog step carries a resolved connection (the Data Source / Initial Catalog recovered from the package's connection managers), emit-adf surfaces that connection on the pipeline as parameters rather than baking it into the T-SQL. For a connection named StagingDB (Data Source=sql-stg-01;Initial Catalog=Staging) it declares pipeline parameters StagingDB_Database (defaultValue: "Staging") and — when a server was resolved — StagingDB_Server (defaultValue: "sql-stg-01") in properties.parameters, binds each into the catalog activity's scripts[0].parameters as @pipeline().parameters.<name>, and injects a matching set_execution_parameter_value line (object type 20, the catalog Project scope) that references the script variable (@StagingDB_Database), placed before start_execution — the same machinery as a promoted /Par parameter. Identical name+value across steps collapses to one declaration (first-wins by step order). Catalog steps without a resolved connection are byte-identical to the pre-CONN-4 output — no parameter is declared or bound.

Connection qualification, end to end

The connection a package reads from is recovered once and then flows through the whole extract → convert-tree → extract-agent-jobs → emit-adf chain, so the generated artifacts reference the source database explicitly instead of relying on an implicit default:

1. convert-tree qualifies the source object. With CONVERT_QUALIFY_FROM_CONNECTION=true (the default), each component's connection manager is resolved to its database and the emitted T-SQL names the object in full — LoadSales' dbo.SrcSales, read through the StagingDB connection (Initial Catalog=Staging), becomes FROM [Staging].[dbo].[SrcSales]. When the connection does not resolve to a database the bare name is kept ([dbo].[SrcDims]), so the flag never breaks an unresolved package.
2. The proc manifest carries the connections. convert-tree writes each package's distinct resolved connections into its _proc_manifest.json entry as a connections array ({"name","database","server"}), deterministically sorted. This is additive — version stays 1 and legacy manifests still load.
3. extract-agent-jobs stamps the connection onto the rewritten step. When a subsystem = SSIS job step is rewritten to EXEC <proc>;, the manifest entry's primary connection is recorded on the step as connection_db / connection_server / connection_name, round-tripping through the per-job JSON.
4. emit-adf injects the connection as a pipeline parameter. The catalog Script activity gains the StagingDB_Database (+ StagingDB_Server) pipeline parameters and set_execution_parameter_value lines described above. For a plain TSQL step (not a catalog step) whose connection database appears in the SQL, the activity's text is instead rebuilt as a @concat(...) expression that splices @pipeline().parameters.<conn>_Database in place of the database identifier — turning the static SQL into dynamic content while keeping the default value behaviour-identical.

The result is one parameter per source database, surfaced on the pipeline and overridable per environment, instead of a hardcoded catalog. The mechanics of the @concat rewrite — why the entire text becomes a single expression, how the identifier is spliced, and the quoting rules — are covered in Reformatting an ADF Script Activity to Dynamic Content with concat; this project emits exactly the bracketed-identifier shape from that guide's "Substituting an identifier" example.

Variables provided by the calling pipeline vs. variables it must add

The parent Azure DevOps pipeline's variables: block already publishes:

Variable	Pipeline value
SOURCE_SQL_SERVER	<prod-sql-server> (prod) / <dev-sql-server> (dev)
TARGET_SQL_SERVER	<target-name>.database.windows.net
ENVIRONMENT_NAME	prod or dev (set from branch)

The parent pipeline must additionally declare, on the steps that invoke this CLI:

Variable	Suggested value	Why
BASE_EXPORT_FILE_PATH	$(Build.ArtifactStagingDirectory)/ssis2sp	Working dir on the agent; roots all relative outputs.
BLOB_STORAGE_PATH	https://<acct>.blob.core.windows.net/<container>/$(Build.BuildNumber)	Final upload target for the generated .sql tree (blank ⇒ upload skipped).
LOG_VERBOSITY	1	INFO-level pipeline logs.

MSSQL_SOURCE_DSN / MSSQL_TARGET_DSN are needed only to override the derived validation DSNs. Every other variable is optional — declare one only to change its default.

Example step:

- script: uv run data_dp_ssis2sp --business-name awb --environment-name dev extract-packages
  env:
    SOURCE_SQL_SERVER: $(sqlServer)
    PACKAGE_STORE: $(packageStore)
    BASE_EXPORT_FILE_PATH: $(Pipeline.Workspace)   # packages land in $(Pipeline.Workspace)/packages

extract-packages / extract-agent-jobs require a host that can do Windows Integrated auth against the domain SQL Server (a self-hosted Windows agent whose service account has read access; Microsoft-hosted Linux agents cannot join the domain). The agent needs ODBC Driver 18 and uv installed once.

Interactive front-ends

A single Textual app (Ssis2SpApp) wraps every operation — conversion, extraction, validation, and the developer gates — each shelling out to the same CLI. It is reachable two ways, both separate entry points from the bare data_dp_ssis2sp command (which stays CLI-only): in the terminal, and in the browser via textual-serve, which streams the very same TUI to an xterm.js browser terminal — there is no separate HTML web UI to maintain.

uv run data_dp_ssis2sp-ui         # the Textual app in the terminal
uv run data_dp_ssis2sp-web        # the same app served in the browser on http://127.0.0.1:8765

The Testing tab carries the two table-parity cards next to the Differential card — Table parity — checksum (compare-tables) and Table parity — + Great Expectations (compare-tables --ge) — each with the procs-dir path, the roles and OPENQUERY toggles, and optional linked-server / html-report / filter-file inputs. Both resolve the SOURCE and TARGET servers themselves, so they carry no upload-side toggle; they need the convert-tree _tables.json manifests with both sides populated, and they touch the database (an unreachable server all-skips and exits 0). A --html-report value writes that report file to the repo root. On the Conversion tab, Run deliverables (c7) gains a Run GX expectations toggle that appends --ge (a _expectations worksheet; needs the [ge] extra). All three drive the same CLI subcommands — see Table parity.

As a library

from data_dp_ssis2sp import convert_file, ConvertOptions

result = convert_file("package.dtsx", ConvertOptions(wrap_in_procedure=True))
print(result.sql)

configure_logging(level="DEBUG") turns on the (default-off) loguru instrumentation; see data_dp_ssis2sp/observability.py.

Source/destination inventory

package_object_inventory returns a distinct, deterministically sorted list of every source and destination endpoint across one or more parsed packages — answering "which data source, which server/database, which object, source or destination?" per row. Built straight off the parsed model (no transpilation), so it is cheap to run over a whole tree for lineage/audit reporting.

from data_dp_ssis2sp.parser import parse_file
from data_dp_ssis2sp.inventory import package_object_inventory

package = parse_file("package.dtsx")
for obj in package_object_inventory(package):
    print(obj.role, obj.data_source, obj.server, obj.database, obj.object_name)
    # source       SalesDB    sql01  Sales    dbo.Customers
    # destination  StagingDB  sql02  Staging  stg.Customers

Each row is a PackageObject(role, data_source, server, database, object_name):

Field	Meaning
role	"source" or "destination"
data_source	referenced connection manager's name (OLEDB / flat-file connection); "" if unresolved
server	host resolved from the connection string (Data Source / Server); "" if unresolved
database	target catalog resolved from the connection string (Initial Catalog / Database); "" if unresolved
object_name	table read/written (OpenRowset / TableName); "" for a free SQL command or flat file

server / database are resolved from the connection string with the same @[$Project::X] parameter fallback the transpiler uses; either is empty when the connection is a flat file or the value hides behind a sensitive/unavailable parameter. Accepts a single Package or any iterable of them; in-pipeline transforms (derived column, lookup, sort, …) are skipped, and identical endpoints are deduplicated across all packages.

Extracting packages from SQL Server

Two stores are supported, chosen by PACKAGE_STORE (auto probes DB_ID('SSISDB') and prefers the catalog):

Store	Source	On disk
msdb	msdb.dbo.sysssispackages (the packagedata column is the .dtsx)	<out>/<folder>/<name>.dtsx
ssisdb	the SSIS catalog — packages live inside .ispac project archives fetched via catalog.get_project and unzipped	<out>/<folder>/<project>/<name>.dtsx

Alongside the .dtsx tree the command writes _packages_manifest.json and, when a package is skipped, _packages_warnings.log.

Project-deployment model. convert-tree auto-detects an expanded project (any directory containing @Project.manifest) and threads project/package parameters and shared connection managers through conversion — typed DECLAREs with real defaults; sensitive/encrypted values become NULL with a warning. The default PACKAGE_EXPANDED=true makes extract → convert-tree a lossless round-trip.

How it works

Stage	Module	Responsibility
Parse	parser.py	.dtsx XML → intermediate representation
Graph	graph.py	components + paths → a topologically-ordered DAG
Transpile	transforms/	one transpiler per component kind → a relation (CTE)
Generate	generator.py	assemble CTEs → one consolidated statement per sink

CTE flattening. After each destination's WITH … INSERT … SELECT statement is assembled, a sqlglot post-pass (flatten.py) collapses the single-consumer CTE chain into one set-based statement: a lookup reference CTE read by a single consumer is inlined so its dimension join appears as a direct LEFT JOIN, while a CTE read by more than one consumer (e.g. a conditional split feeding two branches) is left in place. Join semantics are preserved exactly (INNER stays INNER, LEFT stays LEFT) and the set of physical tables referenced is unchanged. The pass is conservative — a statement it cannot safely flatten (unparseable text, INSERT … EXEC, a recursive CTE, or one whose columns it cannot resolve) is returned unchanged with an advisory warning. Schema-backed flattening. When a schema_snapshot (db.schema.table → columns) is available it is threaded end-to-end — convert_file, convert_tree, and the per-flow split path all funnel it through convert_package into the flattener — and used to qualify columns and normalise mixed-nesting table references, so a degraded chain that would otherwise bail on an unresolved column collapses too. The snapshot only ever enables extra collapsing: an empty or absent snapshot (the air-gapped path) is byte-identical to the heuristic-only output, and a partial snapshot that cannot resolve a statement leaves it unchanged rather than emitting wrong SQL. Flattening is always on in production: the CLI constructs ConvertOptions() and takes the default (flatten_ctes=True). ConvertOptions(flatten_ctes=False) is a code-level escape hatch (there is no env var or setting for it) that reproduces the pre-flatten output byte-for-byte; it exists for tests. Flattened statements are re-rendered by sqlglot, so their formatting differs from the hand-formatted unflattened output.

Numeric ignore-failure lookup columns → ISNULL(<col>, 0). A column retrieved from a lookup's reference table by an ignore-failure lookup (emitted as a LEFT JOIN, so unmatched rows null the reference side) is wrapped ISNULL(<ref>.<col>, 0) in the match projection when its resolved SSIS type is numeric — the integer / decimal / money / float family (i1–i8, ui1–ui8, r4 / r8, numeric, decimal, cy). A fail-on-no-match lookup (INNER JOIN) never nulls the reference side and is left unwrapped; non-numeric columns, and columns whose type cannot be resolved, are also left unwrapped (conservative). The rule is projection-level, upstream of the flattener, so it applies in both flatten modes. This adopts the Acctran optimisation doc's unknown-key pattern scoped to numeric columns only — reversing the earlier SSIS-fidelity exclusion (open question Q5); the always-LEFT JOIN half of that pattern remains excluded.

Orchestrator-only main.dtsx collapse. A main.dtsx that is a pure control-flow orchestrator (zero Data Flow Tasks, only ExecutePackageTasks resolving to siblings) becomes a single proc whose body is the ordered EXEC sequence. CONVERT_NO_ORCHESTRATOR=true (and mixed-mode mains) fall back to dual-file output.

Agent-step rewriting. convert-tree writes _proc_manifest.json mapping each converted .dtsx to its procedure name. With PROC_MANIFEST_PATH set, extract-agent-jobs rewrites subsystem = SSIS job steps to subsystem: TSQL, command: EXEC <proc>;. Unresolved/unparseable/ambiguous steps pass through verbatim with an entry in <out>/_agent_warnings.log.

To extend: subclass Transpiler, register it against a ComponentKind in transforms/, and import it from transforms/__init__.py.

Supported components

SSIS component	T-SQL translation
OLE DB / ADO.NET / ODBC / Excel / XML / Flat FileSource	base CTE —SELECT … FROM table or SQL command
Derived Column	computed columns from translated SSIS expressions
Data Conversion	CAST(…) columns
Copy Column	duplicated columns
Conditional Split	one filtered CTE per output; first-match-wins via negation
Lookup	reference CTE +LEFT JOIN; no-match output as an anti-join
Aggregate	GROUP BY with SUM / AVG / MIN / MAX / COUNT / COUNT(DISTINCT)
Sort	ORDER BY (applied at a destination it feeds directly)
Union All / Merge	UNION ALL
Merge Join	INNER / LEFT / FULL OUTER JOIN
Multicast	shared-CTE reuse
Row Count	pass-through (the variable assignment is dropped)
Audit	system-context columns (SYSDATETIME(), HOST_NAME(), …)
OLE DB / Flat FileDestination	terminal INSERT INTO … SELECT
Character Map / Script / Pivot / Unpivot / OLE DB Command / SCD	pass-through + warning

SSIS expression translation

The Derived Column / Conditional Split expression language has its own lexer, parser, and translator (expressions/):

SSIS	T-SQL
==, !=	=, <>
&&, ||, !	AND, OR, NOT
ISNULL(x)	x IS NULL (a boolean — not a coalesce)
REPLACENULL(a, b)	COALESCE(a, b)
cond ? a : b	CASE WHEN cond THEN a ELSE b END
(DT_STR,n,cp) x	CAST(x AS VARCHAR(n))
TRIM(x)	LTRIM(RTRIM(x))
DATEPART("yyyy", d)	DATEPART(year, d)
"text"	N'text' (control characters spliced as NCHAR(n))

Comparisons used as values become CASE WHEN … THEN 1 ELSE 0 END; bare values used as predicates become … <> 0.

Behaviour notes & limitations

data_dp_ssis2sp aims for behaviour equivalence and flags every place it cannot guarantee it — read the warnings (stderr + the SQL header).

• Lookups are emitted as LEFT JOIN. A lookup configured to fail on a missing match is closer to an INNER JOIN; a warning marks each one. On an ignore-failure (LEFT JOIN) lookup, numeric retrieved columns are wrapped ISNULL(<col>, 0) (see conversion behaviour above).
• Error outputs have no set-based equivalent and are dropped.
• Sort order only survives if the Sort feeds a destination directly.
• Row Count variable assignments are dropped.
• Control-flow (precedence constraints, loops, Execute SQL Tasks) is not converted; Execute SQL Tasks are copied into the output as comments.
• Character Map / Script / Pivot / Unpivot / OLE DB Command / SCD become pass-throughs with a warning; they need manual rework.
• Package variables referenced by expressions become DECLAREd parameters; confirm their types and values before running.

Testing

uv run pytest                                                 # the test suite
uv run pytest --cov=data_dp_ssis2sp --cov-report=term-missing # with coverage
uv run ruff check .                                           # lint
uv run mypy data_dp_ssis2sp validation                        # typecheck

On Windows, run.bat wraps the quality gates and housekeeping behind named targets (run.bat check = lint → typecheck → test), plus the pipeline target for the full extraction chain. Run run.bat with no target for the list.

Validation

validation/ is a differential harness verifying the converted T-SQL produces results identical to the SSIS package's own execution, in three layers:

Layer	Command	Requires
Static	uv run pytest validation/test_static.py	Nothing — pure analysis
Unit	uv run pytest validation/tests	Nothing — no SQL Server
Differential	uv run pytest validation/ -m validation	SQL Server + golden fixtures
Table parity	uv run pytest data_dp_ssis2sp/parity_suite -m parity	SQL Server on both sides + _tables.json manifests — see Table parity

validation/corpus/ holds eight packages exercising every supported component kind, each with package.dtsx, schema.sql, seed/, golden/, and ledger.yaml. Golden output is captured on a Windows host with dtexec — see validation/capture/RUNBOOK.md.

The differential and capture layers connect via two roles configured by MSSQL_TARGET_DSN / MSSQL_SOURCE_DSN (full ODBC connection strings, read straight from the process environment — no .env), or derived from TARGET_SQL_SERVER / SOURCE_SQL_SERVER as described above.

CI (.github/workflows/validation.yml) runs the static and unit layers on every push/PR; the differential layer needs an operator-provisioned SQL Server. azure-pipelines.yaml is this repo's own CI: lint, typecheck, and unit suite with coverage.

Table parity — checksum + Great Expectations

A fourth validation layer comparing the same production table on the SOURCE server (legacy SSIS output) and the TARGET server (transpiled proc output), per the methodology in docs/sample_sql.md (full design in docs/plans/plan-table-parity-pytest.md):

1. Checksum layer (no new dependencies): a server-side CHECKSUM_AGG(BINARY_CHECKSUM(*)) aggregate gate per table; on mismatch, a per-key BINARY_CHECKSUM(*) drill-down names the missing / extra / changed keys (capped at 50).
2. Great Expectations layer (optional [ge] extra, opt-in via --ge): each side's column inventory is read from INFORMATION_SCHEMA.COLUMNS first — a cross-side column-set mismatch fails the table immediately, naming the missing/extra columns, before any expectation runs. Otherwise the SOURCE side's metrics (row count, per-column null/distinct counts, numeric sums) are profiled and pinned into an expectation suite validated against the TARGET — column-level diagnostics where the checksum layer gives row-level keys, with a relative float tolerance (default 1e-9, per-table float_epsilon override in the filter file) where bit-exactness is the wrong bar: a checksum-layer failure with a passing GX layer is the signature of benign float accumulation-order differences between the SSIS and T-SQL implementations.

The GX layer is opt-in twice over: install pip install 'data_dp_ssis2sp[ge]' (the repo's dev dependency group already includes it) and pass --ge. Without --ge the GX tests are not collected at all; with --ge but without the extra installed they skip, naming the extra in the reason.

Tables are discovered from the _deliverables/<proc>/_tables.json manifests written by convert-tree; destination tables are compared by default. run-deliverables --ge (or RUN_DELIVERABLES_GE=1) runs the same GX layer per proc directory and appends the results as a final _expectations worksheet of its workbook.

# run the parity suite (marker: parity; skips cleanly when no server reachable)
uv run pytest data_dp_ssis2sp/parity_suite -m parity --procs-dir stored_procedures
just parity                                    # same, via justfile

# add the Great Expectations layer on a direct pytest run (needs the [ge] extra)
uv run pytest data_dp_ssis2sp/parity_suite -m parity --ge --procs-dir stored_procedures

# one-command CLI wrapper — the suite ships in the wheel, so this runs from an
# installed package too (pip install 'data_dp_ssis2sp[parity]'), any directory
uv run data_dp_ssis2sp --business-name awb --environment-name dev \
    compare-tables --procs-dir stored_procedures \
    [--filter-file filters.json]      # per-table WHERE slice / float_epsilon
    [--roles destination|all]         # default: destination tables only
    [--via-openquery --linked-server HOVMDW]   # literal OPENQUERY mimic mode
    [--ge]                            # add the Great Expectations layer
    [--junit-xml out.xml]             # CI-oriented XML results
    [--html-report parity-report.html]  # self-contained HTML report

# HTML report on a direct pytest run (pytest-html, in the [parity] extra)
uv run pytest data_dp_ssis2sp/parity_suite -m parity --html=parity-report.html --self-contained-html

--html-report forwards --html=<path> --self-contained-html to the pytest-html plugin: one self-contained HTML file covering both layers — every checksum and GX test with pass/fail/skip, durations, and the full failure text per table. Connections resolve MSSQL_TARGET_DSN / MSSQL_SOURCE_DSN first (as above), then fall back to per-database tier routing from ENVIRONMENT_NAME / BUSINESS_NAME; an unroutable database, missing environment, or unreachable server skips the affected tables (exit 0) rather than failing.

OPENQUERY mode (--via-openquery --linked-server <name>): by default the suite opens one direct connection per side; this mode instead reads the source side through the target server — every source-side query is wrapped as OPENQUERY([<name>], '...') (embedded quotes doubled) and run on the target connection, the literal docs/sample_sql.md pattern. Use it when only a server-to-server firewall path exists: the machine running the suite never contacts the source server. Prerequisite: <name> must be a linked server already configured on the target server pointing at the source server (e.g. via sp_addlinkedserver), whose login can read every compared table. --via-openquery without --linked-server fails with a clear message. The GX layer has no OPENQUERY mode — it always connects to each side directly.

SharePoint list migration — emit-adf-sharepoint

Generates ADF Git-format JSON artifacts to copy on-premises SharePoint Server lists → ADLS Gen2 (parquet) using an OData/SHIR source and a Parquet sink. Driven entirely by GlobalSettings; no per-subsite flags are needed at the command line.

Command

data_dp_ssis2sp \
  --business-name awb \
  --environment-name dev \
  emit-adf-sharepoint \
  --out-dir <output-root>

--out-dir is required. One subdirectory per subsite is written under it:

<out-dir>/
  pl_sp_<subsite>/
    linkedService/<target_linked_service>.json   # AzureBlobFS target, Key Vault account key
    dataset/ds_sp_<list>.json                    # ODataResource source (one per list)
    dataset/ds_adls_<list>.json                  # Parquet sink (one per list)
    pipeline/pl_sp_<subsite>.json   # one Copy activity per list
  pl_sp_<subsite_2>/
    …

Exit codes: 0 = success, 2 = I/O or configuration error.

How it works

1. Reads cfg.settings.sharepoint_migration_targets — a list of SharepointMigrationTarget objects, one per on-prem subsite, built from the DEFAULT_SHAREPOINT_LISTS class variable in GlobalSettings.
2. For each target, constructs a SharepointConvertConfig (field names match 1:1) and calls convert_sharepoint(out_dir / pipeline_name, cfg_obj).
3. Prints a per-subsite summary line to stdout.

The subsite list and all resolved names (linked services, pipeline names, Key Vault secrets) come from GlobalSettings — they are not environment variables and cannot be overridden at the command line. To change them, update GlobalSettings.DEFAULT_SHAREPOINT_LISTS or the relevant sp_* properties.

Run-once / no trigger

The emit-adf-sharepoint command is a one-shot migrate path: it emits a pipeline that copies each list's current data to ADLS once. No trigger is generated. Run the pipeline manually from ADF Studio (or an ADO pipeline step) when you want to execute the migration. The ADLS output is deterministic — running the pipeline again overwrites the parquet files in place.

Prerequisites

• Self-Hosted Integration Runtime (SHIR) registered in the data factory and installed on a host with network line-of-sight to the on-prem SharePoint Server (http://sharepoint.example/…). The SHIR is referenced by the existing integration_runtime setting (SelfHostedIntegrationRuntime by default). The SHIR host must have a JRE or OpenJDK installed (required by ADF for Parquet format with snappy compression on a self-hosted IR).
• Key Vault secret holding the ADLS Gen2 account key, referenced by sp_target_account_key_secret (defaults to "PENDING" — update before deploying the generated JSON).
• The ADLS Gen2 file system (container) named by sp_target_filesystem (defaults to "sharepoint" — confirm before publishing).
• The source OData linked service ({name_prefix}sharepoint_source) and its Key Vault-backed password secret must already exist in the factory (generated by generate-linked-services).

Open questions (operator confirms before publish)

Field	Default	Confirm
target_account_key_secret	PENDING	replace with the real KV secret name
target_filesystem	sharepoint	verify the ADLS container name

SQL dataset register migration — emit-procs-from-register / emit-adf-from-register

Converts the SSRS embedded-SQL dataset register (docs/SQL Dataset Text Register(SQL Dataset Conversion).csv) into one CREATE OR ALTER PROCEDURE per dataset (schema reporting.) plus one per-source ADF validation pipeline. The register is read as cp1252 (not UTF-8); the header is row 4 and 196 datasets follow.

Delivered by the REG plan (docs/plans/register-proc-adf-reg-1.md).

Populating the stored-procedure parameters

The register's Command Text carries @param tokens but no parameter types (and its Parameter Count column is unreliable — 128/196 rows disagree with the actual @token count). A valid CREATE PROCEDURE needs typed parameters, so typing is driven by a generated, reviewable document — you do not author it by hand.

1. Generate the procs and the type map. The first run pre-fills types heuristically, so it never blocks on input:

   data_dp_ssis2sp \
     --business-name awb \
     --environment-name dev \
     emit-procs-from-register \
     --register "docs/SQL Dataset Text Register(SQL Dataset Conversion).csv" \
     --out-dir stored_procedures \
     --param-map docs/parameter_type_map.csv
   

   Writes stored_procedures/reporting/*.sql (one per dataset) and docs/parameter_type_map.csv (one row per distinct @param, 59 in total).

2. Review docs/parameter_type_map.csv:

   param_name,occurrences,example_datasets,suggested_type,final_type
   @StartDate,51,"SQL-003;SQL-008",DATE,
   @EndDate,51,"SQL-003;SQL-008",DATE,
   @LoanSource,11,"SQL-045",NVARCHAR(255),
   @DefinitionCode,2,"SQL-077",INT,
   

   • suggested_type — heuristic guess: name matches date|month|period|year|from|to|start|end → DATE; matches *id/*key → INT; otherwise NVARCHAR(255).
   • final_type — your override. Leave blank to accept suggested_type; set a value and it wins.

3. Correct only the wrong guesses. A blank final_type resolves to suggested_type, so the procs work untouched. Edit final_type where the heuristic is wrong (e.g. @LoanSource → INT, @Branch → NVARCHAR(50)).

4. Re-run the same command. Procs regenerate with your types. Generation is idempotent — an unchanged map yields byte-identical .sql.

Resolution rule: final_type when non-empty, else suggested_type. Each parameter is declared @Name <type> = NULL, in first-appearance order within its proc. The reporting. schema and the exact (mixed-case) Proposed Procedure Name from the register are preserved verbatim — emitted as [reporting].[usp_…].

Loading the per-database proc tree

emit-procs-from-register writes a per-database tree:

stored_procedures/
  DW_SHAREPOINT/
    reporting/*.sql
  DW_PRESENTATION/
    reporting/*.sql
  _unrouted/          ← procs whose source maps to no known database
    *.sql

Before loading, check that _unrouted/ is empty (all sources resolved). If it contains any .sql files load-stored-procedures will hard-fail:

# Deploy the per-DB tree — one connection opened per database directory.
data_dp_ssis2sp \
  --business-name awb \
  --environment-name dev \
  load-stored-procedures \
  --procs-dir stored_procedures

The loader opens one connection per database directory, runs CREATE SCHEMA [reporting] if absent, and executes all .sql files under that subtree (sorted path order). Sources in _unrouted/ must be mapped to a real database in the register before deploying.

Per-source ADF validation pipelines

After the procs are deployed, emit one validation pipeline per canonical data source:

data_dp_ssis2sp \
  --business-name awb \
  --environment-name dev \
  emit-adf-from-register \
  --register "docs/SQL Dataset Text Register(SQL Dataset Conversion).csv" \
  --out-dir adf \
  --max-activities 40

Each pipeline holds one SqlServerStoredProcedure activity per proc (EXEC with default NULL parameters) to prove the proc is callable. The register's ~30 Data Source spellings are canonicalised to a handful of physical databases and mapped to {source_linked_service_prefix}<db> linked services. A source's procs are chunked at --max-activities (default 40) across validate_<src>.json, validate_<src>_2.json, … to stay under ADF's 80-activity-per-pipeline ceiling.

License

MIT.