SSIS DevOps and Automation
Why this topic matters
Section titled “Why this topic matters”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.
Fundamentals: what “done” looks like
Section titled “Fundamentals: what “done” looks like”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.
Source control
Section titled “Source control”What goes in
Section titled “What goes in”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 definitionWhat does not go in: the built .ispac, bin/ and obj/ output, and anything containing a real credential.
Living with .dtsx diffs
Section titled “Living with .dtsx diffs”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
.dtsxconflict 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.
Branching
Section titled “Branching”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.
Building
Section titled “Building”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.
The protection-level constraint
Section titled “The protection-level constraint”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.
Build stage checklist
Section titled “Build stage checklist”- Restore any dependencies the project needs.
- Build every
.dtproj, failing the build on any error. - Version the artifact with the build number —
RetailAnalytics-1.4.7.ispac, notRetailAnalytics.ispac. - Publish the
.ispacfiles and the environment definitions and the deployment scripts as pipeline artifacts. The deployment needs all three. - Lint the SQL alongside it — the stored procedures are part of the deliverable.
Deploying
Section titled “Deploying”Deployment automation has three parts, and skipping the third is why “automated” deployments still need a human.
- Deploy the project — push the
.ispacinto the target SSISDB folder. - Configure the catalog — create or update folders, environments, environment variables, and parameter bindings.
- 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 realistic end-to-end pipeline
Section titled “A realistic end-to-end pipeline”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, timestampDesign 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.
Testing SSIS
Section titled “Testing SSIS”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 executionSET NOCOUNT ON;DECLARE @errors INT = 0, @msg NVARCHAR(400) = N'';
-- 1. Expected row countIF (SELECT COUNT(*) FROM dbo.FactSales WHERE BatchId = @BatchId) <> @ExpectedRowsBEGIN SET @errors += 1; SET @msg += N'row count; '; END
-- 2. No orphan dimension keysIF 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 toleranceIF (SELECT COUNT(*) FROM dbo.EtlReject WHERE BatchId = @BatchId) > @RejectToleranceBEGIN SET @errors += 1; SET @msg += N'reject count; '; END
-- 4. ReconciliationIF 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 reasonsIF (SELECT COUNT(DISTINCT RejectReason) FROM dbo.EtlReject WHERE BatchId = @BatchId) < 3BEGIN 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.
Scheduling and orchestration
Section titled “Scheduling and orchestration”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, rollback, and recovery
Section titled “Promotion, rollback, and recovery”Promotion
Section titled “Promotion”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.
Rollback
Section titled “Rollback”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
Section titled “Recovery”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.
Configuration drift
Section titled “Configuration drift”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_nameFROM catalog.projects AS pJOIN catalog.folders AS f ON f.folder_id = p.folder_idORDER BY p.last_deployed_time DESC;Artifact immutability
Section titled “Artifact immutability”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.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”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.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: build per environment
Section titled “Anti-pattern: build per environment”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 buildAnti-pattern: configuration by incident
Section titled “Anti-pattern: configuration by incident”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 scheduleBETTER: 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.Other frequent mistakes
Section titled “Other frequent mistakes”- Committing
.ispacfiles 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 |
Performance and resource considerations
Section titled “Performance and resource considerations”- 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.
Security and configuration considerations
Section titled “Security and configuration considerations”- 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
.ispaccontaining 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_nameandlast_deployed_timegive you the catalog side; the pipeline gives you the rest.
Monitoring after deployment
Section titled “Monitoring after deployment”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 messageSELECT e.execution_id, e.folder_name, e.project_name, e.package_name, e.start_time, MIN(m.message) AS first_errorFROM catalog.executions AS eJOIN catalog.event_messages AS m ON m.operation_id = e.execution_id AND m.message_type = 120WHERE 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_timeORDER BY e.start_time DESC;Alert on, in priority order:
- Failures (status
4). - Cancelled executions (status
3) — frequently missed. - Packages that did not run. No failure alert will ever tell you this.
- Reject ratio crossing a threshold.
- Duration trending upward — a failure that has not happened yet.
- Unexpected deployments — a
last_deployed_timethat does not match a pipeline run means someone deployed by hand.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| 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 |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”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.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Deployment, SSISDB, Environments, and Operations
- SSIS Variables, Parameters, Expressions, Connection Managers, and Configuration
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
- SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
- CI/CD and Pipelines hub
- Database delivery hub
- Liquibase
- Git Branching Strategies
Official Microsoft references
Section titled “Official Microsoft references”- SQL Server Integration Services (SSIS) DevOps Tools Azure DevOps extension
- Standalone SQL Server Integration Service (SSIS) DevOps Tools
- Deploy Integration Services (SSIS) Projects and Packages
- catalog.deploy_project (SSISDB Database)
- catalog.validate_project (SSISDB Database)
- SQL Server Agent Jobs for Packages
- Access Control for Sensitive Data in Packages