Skip to content

SSIS DevOps and Automation

SSIS predates modern CI/CD by a long way, and it shows: packages are XML with layout and GUIDs mixed into the logic, there is no meaningful unit test framework, and the classic deployment story was a person and a wizard.

None of that prevents a proper pipeline. It means the pipeline looks different from an application one — the testing pyramid is inverted, source control needs a social protocol as much as a tool, and the highest-value automation is configuration management rather than build.

Teams that skip this end up with the same three problems every time: nobody knows what is actually in production, environments drift silently, and releases require the one person who remembers the click-path.

A mature SSIS delivery pipeline has five properties. They are worth stating because they are the acceptance criteria for everything below.

Property Means
Reproducible build Any commit can be rebuilt into the same .ispac
Artifact immutability One artifact is built once and promoted; never rebuilt per environment
Configuration as code Environments, variables, and bindings live in source control, applied by the pipeline
Automated verification Build, validate, smoke-run, and assert — without a human
Reversible release A known path back, covering packages and configuration and database objects

The mental model: build once, configure many, verify always

Section titled “The mental model: build once, configure many, verify always”
COMMIT
│
▼
BUILD (once) ──────────────> ARTIFACT: RetailAnalytics-1.4.7.ispac
│ env-dev.json / env-test.json / env-prod.json
│ deploy.sql
▼
DEV ──> deploy artifact + apply dev config + validate + smoke + assert
│
▼
TEST ─> deploy SAME artifact + apply test config + validate + full run + reconcile
│
▼
PROD ─> deploy SAME artifact + apply prod config + validate
(execution belongs to the scheduler, not the pipeline)

The artifact never changes as it moves right. Only the configuration applied alongside it does. If a build happens per environment, the guarantee is gone — what shipped was never what was tested.

repo/
├── src/
│ └── RetailAnalytics/
│ ├── RetailAnalytics.dtproj
│ ├── Project.params
│ ├── *.dtsx
│ └── *.conmgr
├── database/
│ ├── staging/ -- staging schema, control tables
│ └── procedures/ -- the set-based apply procedures
├── deploy/
│ ├── environments/
│ │ ├── env-dev.json
│ │ ├── env-test.json
│ │ └── env-prod.json
│ ├── deploy.sql
│ └── agent-jobs/ -- scripted job definitions
├── tests/
│ ├── seed/ -- known-good input files
│ └── assertions.sql
└── pipeline definition

What does not go in: the built .ispac, bin/ and obj/ output, and anything containing a real credential.

Package XML carries visual layout, regenerated GUIDs, and property ordering that changes when a package is merely opened and saved. The result: enormous diffs for trivial edits, and merge conflicts that are effectively unresolvable by hand.

This is a process problem with a process solution:

  • One developer per package at a time. Treat packages as pessimistically locked even though nothing enforces it. Coordinate in the ticket, not in the merge.
  • Many small packages, not few large ones. The single most effective change — it narrows the collision surface, and improves restartability and parallelism too.
  • Review behaviour, not XML. A meaningful review means opening the package in the designer, or reading the author’s description of what changed and why. Line-by-line XML review is theatre.
  • Never resolve a .dtsx conflict by hand. Take one side wholesale and re-apply the other change in the designer.
  • Keep transformation logic in SQL where you can. Stored procedures diff, review, merge, and test like normal code. This is a real argument for the stage-then-apply pattern beyond performance.
  • Normalise line endings and encoding so the repository does not record whitespace-only churn.

Standard models work, with one adjustment: because packages merge badly, keep feature branches short-lived and integrate often. A branch that lives three weeks while others edit the same packages will end in a manual re-apply. See Git Branching Strategies for the general models.

An SSIS build compiles .dtproj into .ispac. Two ways to do it on an agent.

With Visual Studio — the agent needs Visual Studio plus the SQL Server Integration Services Projects extension, and any third-party or feature-pack components used by the packages. Hosted Windows images that include Visual Studio can work, but component installation makes builds slower and more fragile.

Without Visual Studio — Microsoft’s SSIS DevOps Tools provide a build task (and standalone SSISBuild.exe) that does not depend on Visual Studio or the SSIS designer, requiring only Windows and a supported .NET Framework. It also removes the need to pre-install out-of-box components on the agent — which is the usual reason builds break.

Prefer the second where it fits: fewer moving parts, and a build that does not silently depend on whatever happened to be installed on that agent.

The tooling comes in two forms:

  • The SSIS DevOps Tools extension in the Azure DevOps Marketplace: SSIS Build, SSIS Deploy, and SSIS Catalog Configuration tasks.
  • Standalone executables (SSISBuild.exe, SSISDeploy.exe) callable from any CI system — the option when the build server is not Azure DevOps.

Password-based protection levels (EncryptSensitiveWithPassword, EncryptAllWithPassword) require the password at build time and cause problems with some build task versions. Use DontSaveSensitive and supply sensitive values through catalog parameters instead. The build tooling also offers a “strip sensitive data” option that converts protection level during build — useful when inheriting packages you cannot immediately change.

  1. Restore any dependencies the project needs.
  2. Build every .dtproj, failing the build on any error.
  3. Version the artifact with the build number — RetailAnalytics-1.4.7.ispac, not RetailAnalytics.ispac.
  4. Publish the .ispac files and the environment definitions and the deployment scripts as pipeline artifacts. The deployment needs all three.
  5. Lint the SQL alongside it — the stored procedures are part of the deliverable.

Deployment automation has three parts, and skipping the third is why “automated” deployments still need a human.

  1. Deploy the project — push the .ispac into the target SSISDB folder.
  2. Configure the catalog — create or update folders, environments, environment variables, and parameter bindings.
  3. Validate — confirm the deployed project resolves everything it needs before anything runs on a schedule.

For step 1: SSISDeploy.exe, the SSIS Deploy pipeline task, ISDeploymentWizard.exe in silent mode, or catalog.deploy_project. All do the same thing; pick what fits your agent. The T-SQL route needs only a SQL connection, which makes it the most portable.

For step 2, the SSIS DevOps Catalog Configuration task takes a JSON document describing folders, projects, parameter values, environments, and references, and applies it — creating anything missing. That turns catalog configuration into a versioned file rather than a click-path:

{
"folders": [
{
"name": "Sales",
"description": "Sales domain ETL",
"projects": [
{
"name": "RetailAnalytics",
"parameters": [
{
"name": "StagingDbServer",
"container": "RetailAnalytics",
"value": "StagingDbServer",
"valueType": "referenced"
},
{
"name": "BatchSize",
"container": "LoadSalesFact.dtsx",
"value": 50000,
"valueType": "literal"
}
],
"references": [
{ "environmentFolder": ".", "environmentName": "Production" }
]
}
],
"environments": [
{
"name": "Production",
"variables": [
{ "name": "StagingDbServer", "type": "String",
"value": "__STAGING_DB_SERVER__", "sensitive": false },
{ "name": "InboundFolder", "type": "String",
"value": "__INBOUND_FOLDER__", "sensitive": false }
]
}
]
}
]
}

Note the __PLACEHOLDER__ tokens: server names are configuration and can live in the file, but they still differ per environment. Substitute them from pipeline variables at deploy time. Credentials never appear here at all — they belong in the CI system’s secret store, or are eliminated by using integrated authentication.

If the extension is unavailable, the same result comes from a scripted sequence of catalog.create_environment, catalog.create_environment_variable, catalog.create_environment_reference, and catalog.set_object_parameter_value calls — written idempotently so re-running a release is safe. Deployment, SSISDB, Environments, and Operations has a complete worked script.

The mechanism matters less than the principle: the catalog’s configuration is code, stored in the repository, applied by the pipeline.

For step 3, call catalog.validate_project after deploying and fail the stage on errors. It is cheap and catches the most common deployment defect — a parameter with no value in this environment.

A fictional pipeline for RetailAnalytics, written generically so it maps onto any Windows-capable CI system.

STAGE: build (runs on every commit and pull request)
1. checkout
2. SSISBuild → src/RetailAnalytics/RetailAnalytics.dtproj
output: artifacts/RetailAnalytics-$(BuildNumber).ispac
3. sql lint → database/**/*.sql
4. publish artifacts:
- RetailAnalytics-$(BuildNumber).ispac
- deploy/environments/*.json
- deploy/deploy.sql
- database/** (migrations)
- tests/**
STAGE: deploy-dev (automatic on main)
1. download artifacts
2. apply database migrations → dev staging + procedures
3. SSISDeploy .ispac → SSISDB\Sales on dev
4. apply catalog config env-dev.json (placeholders substituted from pipeline vars)
5. catalog.validate_project → FAIL the stage on any error message
6. smoke run: execute LoadSalesFact against tests/seed with SYNCHRONIZED = 1
7. run tests/assertions.sql → FAIL on any assertion breach
STAGE: deploy-test (automatic after dev succeeds)
1. download the SAME artifacts ← never rebuild
2. apply database migrations → test
3. SSISDeploy .ispac → SSISDB\Sales on test
4. apply catalog config env-test.json
5. validate
6. full run against a representative test dataset
7. reconcile: rows read = rows loaded + rows rejected; control totals match
STAGE: deploy-prod (manual approval gate)
1. download the SAME artifacts
2. apply database migrations → production
3. SSISDeploy .ispac → SSISDB\Sales on production
4. apply catalog config env-prod.json
5. validate
6. deploy scripted Agent job definitions
7. NO execution — the scheduler owns that
8. record: build number, artifact hash, deployer, timestamp

Design decisions worth naming:

  • The artifact is downloaded, never rebuilt, at every stage after the first.
  • Database migrations and package deployment are one unit with one version number, applied in dependency order.
  • Validation is a gate, not a report.
  • Production is not executed by the pipeline. Deployment and scheduling are separate concerns, and a pipeline that runs the nightly load “to check” is a pipeline that will one day run it twice.
  • The release is recorded, so “what is in production?” has an answer that does not require archaeology.

Database changes belong in the same pipeline

Section titled “Database changes belong in the same pipeline”

An SSIS deployment that lands before its staging table exists fails at validation. One that lands after a stored procedure changed shape fails at runtime, in production, at 02:00.

Deploy database objects and packages together, in dependency order, from the same build. A migration tool handles the schema half — the site covers Liquibase — and the SSIS steps handle the package half. What matters is that they share one version number and one rollback story.

There is no unit test framework worth the name, so the pyramid is inverted: most verification is integration-level, and the highest-value test is usually the simplest.

Layer Verifies How Cost
Build The project compiles, components resolve The build step itself Free
Validation Parameters, connections, environment references resolve on the target catalog.validate_project Seconds
Smoke The package runs end to end on known input Execute against a seeded database, SYNCHRONIZED = 1 Minutes
Data assertions It produced the right answer SQL checks after execution Seconds
Regression A known-good input still produces known-good output Store input + expected output; compare Minutes
Error-path Bad rows are handled as designed Seeded input with deliberate defects; assert reject counts Minutes
Idempotence Re-running changes nothing extra Run the same batch twice; assert the target is identical Minutes
-- tests/assertions.sql — run after a smoke or test execution
SET NOCOUNT ON;
DECLARE @errors INT = 0, @msg NVARCHAR(400) = N'';
-- 1. Expected row count
IF (SELECT COUNT(*) FROM dbo.FactSales WHERE BatchId = @BatchId) <> @ExpectedRows
BEGIN SET @errors += 1; SET @msg += N'row count; '; END
-- 2. No orphan dimension keys
IF EXISTS (SELECT 1 FROM dbo.FactSales f
WHERE f.BatchId = @BatchId
AND NOT EXISTS (SELECT 1 FROM dbo.DimStore d WHERE d.StoreKey = f.StoreKey))
BEGIN SET @errors += 1; SET @msg += N'orphan store keys; '; END
-- 3. Rejects within tolerance
IF (SELECT COUNT(*) FROM dbo.EtlReject WHERE BatchId = @BatchId) > @RejectTolerance
BEGIN SET @errors += 1; SET @msg += N'reject count; '; END
-- 4. Reconciliation
IF EXISTS (SELECT 1 FROM dbo.EtlBatch
WHERE BatchId = @BatchId AND RowsRead <> RowsLoaded + RowsRejected)
BEGIN SET @errors += 1; SET @msg += N'unreconciled; '; END
-- 5. Error-path proof: the seeded bad rows WERE rejected, with the right reasons
IF (SELECT COUNT(DISTINCT RejectReason) FROM dbo.EtlReject WHERE BatchId = @BatchId) < 3
BEGIN SET @errors += 1; SET @msg += N'expected reject reasons missing; '; END
IF @errors > 0 THROW 50001, @msg, 1;

Test the error paths, not only the happy path. A seeded file containing deliberate type failures, truncations, and unknown dimension members proves the error handling exists. A pipeline that has only ever seen clean data has untested error handling by definition.

Designing packages to be testable pushes you toward good design anyway: parameterised paths and connections, no hard-coded servers, staging tables you can inspect, and idempotent applies you can run twice.

Deployment and execution are separate concerns. A pipeline deploys; a scheduler runs.

SQL Server Agent is the default on-premises answer. Job steps of type SQL Server Integration Services Package against the catalog give parameter and environment selection directly.

  • Run under a proxy account with only the rights the packages need, rather than granting the Agent service account broad access.
  • The step runs the package synchronously, so the job result reflects the package result.
  • Keep job definitions in source control, scripted and deployed by the pipeline. Jobs created by hand drift exactly as environments do — and a disabled job is invisible to failure alerting.

External orchestrators — enterprise schedulers, workflow engines, or cloud orchestration services — typically invoke packages through dtexec, the catalog stored procedures, or a dedicated activity. Reach for one when SSIS is a step in a wider workflow involving systems SQL Server Agent knows nothing about, or when you need cross-server dependencies.

If you call catalog.start_execution from custom code, set the SYNCHRONIZED execution property — otherwise the call returns as soon as the run is queued and your scheduler will report success for a package that later failed.

A master package is orchestration too, and it is the right level for dependencies within one deployment unit. Use the scheduler for “when”, and a master package for “in what order”.

Promotion means the same artifact moving through environments with different configuration. Practical requirements:

  • Version the artifact filename, so the deployment record is self-describing.
  • Record what was deployed where, including the build number and who approved it.
  • Never allow a “hotfix build” that skips earlier stages. If production needs an urgent fix, it still goes through the pipeline — the stages just run quickly.

Several layers, and the useful one depends on the failure:

Failure Recovery
Package logic wrong Redeploy the previous .ispac from artifacts, or catalog.restore_project
Configuration wrong Re-apply the previous environment definition from source control
Database change wrong The migration tool’s rollback path, or a corrective forward migration
Data wrong Re-run the load — only works if the load is idempotent

The catalog’s version history is a genuinely useful safety net, but note what it does not restore: environments, environment variables, parameter bindings, Agent jobs, or database objects. A rollback plan that only redeploys the project is incomplete.

Forward fixes usually beat rollbacks for database changes, because a rollback that drops a column loses whatever was written to it. Decide which one you are doing before the incident, not during it.

Recovery, as distinct from rollback, is about the data — and it is settled at design time, not deployment time: idempotent applies, batch-scoped staging, and state advanced only after commit. See Incremental Loads and Error Handling and Restartability.

Drift is the slow divergence of environments that were once identical. It is invisible until a deployment behaves differently in production, and it is the single most common cause of “but it worked in test”.

Where it comes from:

Source Example
Manual environment edits A value changed in SSMS during an incident, never reflected in source control
Literal parameter assignments A literal written onto a parameter during troubleshooting, which unbound it from its environment reference
Hand-created Agent jobs Test has a job production does not, or vice versa
Out-of-band package deploys Someone deployed a single package with the wizard
Divergent database schema A hotfix index applied to production only

Detecting and preventing it:

  • Make the pipeline the only writer. If people can edit the catalog by hand, they will.
  • Diff environments between servers as part of release. Two environments with the same name and different variable sets is drift.
  • Audit parameter assignment mode — look for catalog.object_parameters.value_type = 'V' where you expect 'R'; that parameter is no longer bound to its environment variable.
  • Compare deployed project versions across environments; production running an older build than test is either intended or a problem, and you should know which.
  • Re-apply configuration on every deployment, idempotently. Drift then self-heals rather than accumulating.
-- Which build is actually deployed here?
SELECT f.name AS folder_name,
p.name AS project_name,
p.object_version_lsn,
p.last_deployed_time,
p.deployed_by_name
FROM catalog.projects AS p
JOIN catalog.folders AS f ON f.folder_id = p.folder_id
ORDER BY p.last_deployed_time DESC;

The principle: an artifact, once built, never changes. Its contents are fixed, its name carries a version, and every environment receives byte-identical bytes.

Why it matters for SSIS specifically:

  • Packages are hard to review, so “the same build” is the only practical guarantee that what was tested is what shipped.
  • SSIS has weak automated testing, so the value of a test result depends entirely on it applying to the deployed artifact.
  • Rebuilding can genuinely produce different output — regenerated GUIDs, a different extension version on a different agent, a component that was installed on one build machine and not another.

Practical enforcement:

  • Build once, in one stage, and publish the artifact.
  • Downstream stages download, never rebuild.
  • Record the artifact’s hash alongside the deployment record.
  • Treat “just rebuild it for prod” as a defect in the pipeline, not a convenience.

Agent-installed components. Packages using third-party or feature-pack components need those installed on the build agent when building with Visual Studio. The Visual-Studio-free build task removes this requirement — one of its strongest arguments.

Digitally signed packages. The Visual-Studio-free build task does not support building projects containing digitally signed packages. If your estate signs packages, that constrains your build options.

Self-hosted agents. SSIS build and deploy tooling is Windows-only, so a Linux-only CI estate needs at least one Windows agent. This is a real constraint on tooling choice, not a preference.

Generated packages. Some teams generate .dtsx from templates (BIML or custom tooling) to avoid the merge problem entirely. It works, and it moves the review surface to the template — but it adds a build dependency and a skill requirement that outlives the person who introduced it.

Multiple projects, one solution. Build tasks can build a folder recursively. Be explicit about which projects are deployed where; a solution-wide build that deploys everything to every environment is a common accident.

Parallel deployments. Two pipelines deploying to the same SSISDB folder concurrently can interleave configuration steps. Serialise deployments per folder.

BAD:
stage deploy-prod:
- checkout
- build .dtproj ← a NEW artifact, built from source again
- deploy
Different agent, different extension version, regenerated GUIDs.
What shipped was never tested.
BETTER:
stage build: build once, publish RetailAnalytics-1.4.7.ispac
stage deploy-*: download that artifact; never build
BAD:
02:00 incident → write a literal onto the parameter in SSMS → resolved →
forgotten. That write unbound the parameter from its environment variable
(value_type R -> V), and six months of runs use the stale literal.
BETTER:
Change the value in the environment definition in source control,
run the pipeline, and let the idempotent config step apply it.
Emergency changes get a follow-up commit the same morning.

Anti-pattern: the pipeline that runs production

Section titled “Anti-pattern: the pipeline that runs production”
BAD:
deploy-prod stage ends with "execute LoadSalesFact to verify"
→ a redeploy at 18:00 runs the nightly load a second time
→ duplicate data, or a lock fight with the real schedule
BETTER:
Verify with catalog.validate_project.
Execution belongs to the scheduler. If you must run something,
run a read-only health-check package that writes nothing.
  • Committing .ispac files instead of building them.
  • Password-based protection levels blocking automated builds.
  • Secrets in configuration files in the repository.
  • Deploying packages and database changes separately, in inconsistent orders.
  • No post-deployment validation.
  • Long-lived branches touching the same packages.
  • Treating the catalog version history as a complete rollback plan.
  • Agent jobs created by hand, so a disabled job is invisible.

Failure modes, recovery, and troubleshooting

Section titled “Failure modes, recovery, and troubleshooting”
Symptom Likely cause Action
Build fails only on the hosted agent Missing component or extension Use the Visual-Studio-free build task, or install components in a prior step
Build hangs and times out Password-protected project, no password supplied Change protection level to DontSaveSensitive
Deploy succeeds, validation fails Environment variable missing or unbound Read the validation messages — they name the parameter
Package uses wrong values after deploy Parameter holds a literal, so it is unbound from the environment Check catalog.object_parameters.value_type; re-apply config idempotently to rebind
“Works in test, fails in prod” Configuration drift Diff environments; compare deployed project versions
Deployment blocked by permissions Deploy identity lacks create-object on the folder Grant folder-scoped rights only
Scheduler reports success for failed runs SYNCHRONIZED not set on create_execution Set execution property 50 / SYNCHRONIZED
Two deployments interfered Concurrent pipelines on one folder Serialise deployments per SSISDB folder
Rollback did not fix it Versions restore the project only Re-apply environment definitions and database rollback
  • Build time is dominated by tooling startup, not package size. The Visual-Studio-free task is usually faster because it skips loading the designer.
  • Deployment is cheap; validation is cheap. There is no performance argument for skipping them.
  • Test-stage full runs are the expensive part. Use a representative dataset sized for signal, not a production copy — and run them in parallel with other stages where dependencies allow.
  • Do not run heavy test executions against a shared SSISDB that production monitoring also reads; the execution history noise degrades both.
  • The deployment identity is a privileged account. Scope it to the target folder with create-object and modify rights — not ssis_admin.
  • Secrets belong in the CI system’s secret store, injected at deploy time, never in env-*.json.
  • Prefer integrated authentication so there is no secret to inject at all.
  • Pipeline logs echo variables. Mark secret variables as secret in the CI system so substitution does not print them.
  • Artifact stores are a distribution channel. An .ispac containing embedded credentials (from a password protection level) is a credential leak with a long tail.
  • Separate approval from execution. The person who approves a production release should not need the rights to deploy it manually.
  • Audit who deployed what — catalog.projects.deployed_by_name and last_deployed_time give you the catalog side; the pipeline gives you the rest.

A pipeline that deploys but tells nobody how the loads behave is half a solution. The catalog already holds the data:

-- Failures in the last day, with the first error message
SELECT e.execution_id,
e.folder_name,
e.project_name,
e.package_name,
e.start_time,
MIN(m.message) AS first_error
FROM catalog.executions AS e
JOIN catalog.event_messages AS m
ON m.operation_id = e.execution_id AND m.message_type = 120
WHERE e.status = 4
AND e.start_time > DATEADD(DAY, -1, SYSDATETIMEOFFSET())
GROUP BY e.execution_id, e.folder_name, e.project_name, e.package_name, e.start_time
ORDER BY e.start_time DESC;

Alert on, in priority order:

  1. Failures (status 4).
  2. Cancelled executions (status 3) — frequently missed.
  3. Packages that did not run. No failure alert will ever tell you this.
  4. Reject ratio crossing a threshold.
  5. Duration trending upward — a failure that has not happened yet.
  6. Unexpected deployments — a last_deployed_time that does not match a pipeline run means someone deployed by hand.
Decision Option A Option B Choose A when
Build tooling Visual-Studio-free build task Visual Studio on the agent You are not building signed packages
Deploy mechanism catalog.deploy_project (T-SQL) SSISDeploy.exe / pipeline task The agent has SQL connectivity but no SSIS tooling
Catalog config JSON + Catalog Configuration task Scripted T-SQL You are on Azure DevOps
Merge strategy Small packages, exclusive editing Generated packages from templates The team is small and coordination is cheap
Rollback Redeploy previous artifact Forward fix Configuration and schema are unchanged
Test data Small curated seed Production-like copy You need fast, deterministic assertions

Q: Why is artifact immutability more important for SSIS than for a typical application? Because SSIS has weak automated testing and near-unreviewable diffs, the strongest guarantee you have is “this is the same bytes we tested”. Rebuilding can genuinely change output — regenerated GUIDs, a different SSIS extension version on a different agent, a component present on one build machine and not another. With no unit tests to catch a difference, an unnoticed rebuild difference reaches production silently.

Q: How do you handle two developers needing to change the same package? You avoid it structurally rather than solving it technically. Packages merge badly because the XML carries layout and regenerated GUIDs, so the practical answers are: keep packages small so the collision surface is narrow, treat them as exclusively locked and coordinate in the ticket, keep transformation logic in stored procedures that merge properly, and if a conflict does occur, take one side wholesale and re-apply the other change in the designer — never hand-edit the XML.

Q: What does a good SSIS test suite look like given there is no unit test framework? Inverted pyramid. Build (compiles), catalog validation (configuration resolves on this server), smoke run against a curated seed dataset with SYNCHRONIZED = 1, then SQL assertions on the result: expected row counts, no orphan keys, rejects within tolerance, reconciliation (rows read = loaded + rejected). Add an error-path test with deliberately malformed input asserting the right reject reasons appear, and an idempotence test that runs the same batch twice and asserts the target is unchanged.

Q: What is configuration drift and how does a pipeline prevent it? The slow divergence of environments that were once identical — a value edited in SSMS during an incident, a literal left written onto a parameter, a hand-created Agent job, a hotfix schema change applied to one tier. Prevention: make the pipeline the only writer to the catalog, keep environment definitions in source control, re-apply them idempotently on every deployment so drift self-heals, and diff environments between servers as a release check.

Q: A team wants the production pipeline to execute the package after deploying “to make sure it works”. Argue. It conflates deployment with scheduling. A redeploy at 18:00 would run the nightly load a second time — duplicating data if the load is not idempotent, or fighting the real schedule for locks if it is. The correct verification is catalog.validate_project, which proves configuration resolves without side effects. If a runtime check is genuinely needed, run a read-only health-check package that writes nothing.

Q: How do you deploy database changes and SSIS packages together safely? As one deployment unit with one version number, applied in dependency order: schema first (so tables and procedures the packages reference exist), then the .ispac, then catalog configuration, then validation. A migration tool owns the schema half; the SSIS tooling owns the package half. Splitting them across separate pipelines guarantees that one day the orders will differ and validation will fail in production.

Q: Your CI estate is entirely Linux. What is the impact? SSIS build and deployment tooling is Windows-only, so you need at least one Windows agent — self-hosted or a hosted Windows image. The one exception is the catalog.deploy_project T-SQL path, which needs only a SQL connection and so can run from anywhere; but building the .ispac in the first place cannot. This is a hard constraint to surface early in tooling decisions rather than discover mid-migration.

Q: What is the first automation you would introduce into an SSIS estate that has none? Not the build — configuration as code. Getting environments and parameter bindings into source control and applied idempotently kills configuration drift, which causes more production incidents than build reproducibility does. Automated build and deployment follow immediately after, but the drift problem is the one that is actively causing outages while nobody can see it.