Skip to content

SSIS Variables, Parameters, Expressions, Connection Managers, and Configuration

“It works in development” is usually a configuration statement, not a code statement. The goal of this topic is a package that is built once and configured per environment — the same .ispac running in development, test, and production with no edits.

Getting there requires four mechanisms that people routinely confuse: variables, parameters, expressions, and configuration. This article separates them, then shows how they combine.

Variable Parameter
Changes during execution Yes No — fixed for the whole execution
Set from outside the package No Yes
Scope Package, container, or task Package or project
Typical use Row counts, current file name, computed paths, loop state Connection details, folder roots, batch sizes, run-mode flags
Available in All versions SQL Server 2012+, project deployment model

The rule: parameters are the package’s inputs; variables are its working memory.

Parameters arrived with the project deployment model. In the legacy package deployment model everything external came through package configurations, which is why old packages use variables where a modern package would use a parameter.

Every variable has a name, namespace, data type, scope, and value.

  • Namespaces — User:: for yours, System:: for the engine’s.
  • Useful system variables — System::PackageName, System::StartTime, System::ExecutionInstanceGUID, System::MachineName, System::UserName, System::ErrorCode, System::ErrorDescription, System::SourceName, System::TaskName.
  • EvaluateAsExpression — set True and give the variable an expression; its value is recomputed each time it is read rather than stored.
Variable: User::ArchiveFolder (String, EvaluateAsExpression = True)
@[$Project::ArchiveRoot] + "\\" +
(DT_WSTR, 4) YEAR(@[System::StartTime]) +
RIGHT("0" + (DT_WSTR, 2) MONTH(@[System::StartTime]), 2) +
RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)

RaiseChangedEvent — when True, changing the variable fires OnVariableValueChanged. Occasionally useful for auditing; noisy if left on broadly.

  • Project parameters (Project.params) — defined once, visible to every package. Use for anything shared: connection details, root folders, environment name, notification addresses.
  • Package parameters — visible to one package. Use for genuinely package-specific inputs: a source table name, a package-specific batch size.

Two flags matter:

Flag Effect
Required No usable design-time default; a value must be supplied at execution. Good for forcing an explicit environment choice
Sensitive Value encrypted in the catalog, masked in reports and logs. Use for anything credential-shaped

The mental model: four mechanisms, one flow of values

Section titled “The mental model: four mechanisms, one flow of values”
OUTSIDE THE PACKAGE INSIDE THE PACKAGE
SSISDB environment variable
│ bound by an environment reference
▼
PARAMETER ──────────────┐
(fixed for this execution) │
├──> EXPRESSION ──> a property, a variable,
VARIABLE ───────────────┘ a precedence constraint,
(working memory, mutable) a derived column
PROPERTY EXPRESSION
rewrites a component property
at runtime — the mechanism behind
everything "dynamic" in SSIS

Read that top to bottom and most configuration questions answer themselves: environments feed parameters; parameters and variables feed expressions; expressions rewrite properties.

Make one hard-coded path configurable.

BEFORE
Flat File Connection Manager "SourceFile"
ConnectionString = "D:\Inbound\stores.csv" ← hard-coded, edited per environment
AFTER
Project parameter: InboundFolder (String) = "D:\\Inbound"
Variable: User::SourceFilePath (String, EvaluateAsExpression = True)
@[$Project::InboundFolder] + "\\stores.csv"
Flat File Connection Manager "SourceFile"
Expressions → ConnectionString = @[User::SourceFilePath]

The package now builds once and runs anywhere the parameter is set. That is the whole idea, applied everywhere else in this article.

A variable belongs to the package or to a specific container/task. Inner scopes see outer variables; outer scopes cannot see inner ones.

Package
├── User::BatchId visible everywhere
└── Foreach Loop Container
├── User::CurrentFile visible only inside the loop
└── Sequence Container
└── User::RowCount visible only inside the sequence

Shadowing is the trap. Two variables with the same name in different scopes both exist; a task reads the nearest one in scope. Symptoms: a value you clearly set is “not there”, or a Row Count writes to a variable an outer task never sees.

BAD:
Package → User::RowCount = 0
└── Foreach Loop → User::RowCount = 0 ← same name, different variable
└── Row Count writes the INNER one
Package-level Execute SQL Task logs the OUTER one → always 0

Practical rules:

  • Default to package scope with distinct names.
  • Use container scope only where a loop genuinely needs its own counter.
  • Never reuse a name across scopes. The designer will let you; nothing will warn you.
  • When debugging, check the Variables window’s Scope column, not just the name.

SSIS expressions are a small, strict language used in variables, property expressions, precedence constraints, Derived Columns, and Conditional Splits. They are not T-SQL, and the differences bite.

"literal string" -- double quotes, always
@[User::MyVar] -- variable
@[$Project::FolderRoot] -- project parameter
@[$Package::BatchSize] -- package parameter
@[System::StartTime] -- system variable
a == b a != b -- equality / inequality (no single =)
a && b a || b !a -- logical and / or / not
cond ? x : y -- conditional (no CASE)
(DT_I4) "42" -- cast to four-byte integer
(DT_WSTR, 50) @[User::Id] -- Unicode string, length 50
(DT_STR, 50, 1252) @[User::Name] -- non-Unicode with code page
(DT_DBTIMESTAMP) @[User::WhenText] -- timestamp
(DT_DECIMAL, 4) @[User::Rate] -- decimal with scale
Category Functions
String SUBSTRING, LEN, LEFT, RIGHT, TRIM, LTRIM, RTRIM, UPPER, LOWER, REPLACE, FINDSTRING, REPLICATE, REVERSE
Date GETDATE, GETUTCDATE, DATEADD, DATEDIFF, DATEPART, YEAR, MONTH, DAY
Null ISNULL, NULL(DT_I4), REPLACENULL
Numeric ABS, CEILING, FLOOR, ROUND, SIGN, POWER, SQUARE
Type (DT_*) casts, HEX
  • ISNULL tests, it does not substitute. T-SQL: ISNULL(a, b) returns b when a is null. SSIS: ISNULL(a) returns a Boolean. Substitution is ISNULL(a) ? b : a — or REPLACENULL(a, b), which exists precisely to reduce this confusion.
  • Backslashes must be doubled in string literals: "D:\\Data\\Inbound". A single backslash silently produces an escape sequence.
  • No implicit conversion. Comparing DT_WSTR to DT_STR, or a string to a number, fails validation rather than coercing.
  • String results are capped at 4000 characters. Long dynamic SQL built in an expression is silently truncated — build it in a Script Task or a stored procedure.
  • Boolean literals are True/False, not 1/0.
  • Integer division truncates. (DT_I4)7 / (DT_I4)2 is 3. Cast to a decimal type first.
  • Date arithmetic returns DT_DBTIMESTAMP; comparing it to a DT_DBDATE column needs an explicit cast.

Every expression editor has an Evaluate Expression button. Use it. An expression that only fails at runtime, inside a loop, on the third file, is expensive to diagnose — and expression errors in property expressions surface as obscure component failures rather than clear syntax errors.

A property expression sets a component property dynamically at runtime. This is the mechanism behind essentially everything “dynamic” in SSIS.

Set them through the Expressions collection in the Properties window of a task, container, connection manager, or the package itself.

Property expression on Sets Enables
Flat File connection manager → ConnectionString The file path Iterating files in a Foreach Loop
OLE DB connection manager → ConnectionString The whole connection string Environment-specific targets
Execute SQL Task → SqlStatementSource The statement text Metadata-driven SQL
File System Task → Destination The target path Date-stamped archive folders
Send Mail Task → Subject / MessageSource Notification content Run-specific alerts
For Loop Container → EvalExpression The loop condition Data-driven iteration counts
Foreach Loop → Directory The folder to enumerate Environment-specific inbound roots
Any task → Disable Whether the item runs Feature toggles per environment

When property expressions are evaluated: at the point the property is needed — for a connection manager, each time the connection is acquired; for a task property, before that task executes. That is exactly why the Foreach Loop pattern works: the connection string is re-evaluated on every iteration.

1. Foreach Loop Container (Foreach File enumerator)
Directory: set by a property expression = @[$Project::InboundFolder]
Files: *.csv
Retrieve file name: Fully qualified
Variable Mappings: index 0 → User::CurrentFilePath
2. Flat File Connection Manager "SourceFile"
Expressions → ConnectionString = @[User::CurrentFilePath]
3. Inside the loop:
Data Flow Task reading from "SourceFile" DelayValidation = True
File System Task moving User::CurrentFilePath to @[User::ArchiveFolder]

Two settings make it work reliably:

  • Point the connection manager at a real sample file at design time so column metadata can be inferred. The expression replaces the path at runtime; the metadata must remain valid for every file.
  • Set DelayValidation = True on the Data Flow Task, because at package start the variable is empty and validation against a non-existent path would fail.

Two supported approaches, and it is worth knowing why the newer one usually wins.

Property expression on ConnectionString — flexible, works for any connection manager type, lets you compose from several parameters. Downside: the whole string is one opaque value, and a typo produces a runtime connection error rather than a clear configuration error.

Parameterise the connection manager directly — right-click → Parameterize. SSIS exposes individual properties (ServerName, InitialCatalog, ConnectionString) as parameters. In SSISDB these appear as CM.<ConnectionManagerName>.<PropertyName>, which an environment can bind directly.

Project parameters:
StagingDbServer = "sql-staging-01"
StagingDbCatalog = "StagingDb"
Connection manager "StagingDb", parameterised:
CM.StagingDb.ServerName → $Project::StagingDbServer
CM.StagingDb.InitialCatalog → $Project::StagingDbCatalog

Prefer the second for catalog deployments: values are visible and configurable in SSISDB without opening the package, and automated catalog configuration can set them by name. See Deployment, SSISDB, Environments, and Operations.

  • Mark credential parameters Sensitive — encrypted in the catalog, masked in reports.
  • Set the package ProtectionLevel = DontSaveSensitive so nothing secret is stored in the .dtsx at all.
  • Prefer integrated authentication (Windows or Entra) so there is no secret to store, mask, or rotate.
  • Remember that sensitive parameter values are protected by the SSISDB database master key — which is why the catalog password matters when restoring to another server.
  • A sensitive value assigned into a non-sensitive variable loses its protection and can appear in logs. Do not launder secrets through variables.

A fictional retail load configured for three environments with no package edits.

PROJECT PARAMETERS (Project.params)
InboundFolder String Required e.g. "D:\\Retail\\Inbound"
ArchiveRoot String Required e.g. "D:\\Retail\\Archive"
QuarantineRoot String Required e.g. "D:\\Retail\\Quarantine"
StagingDbServer String Required
StagingDbCatalog String default "StagingDb"
WarehouseServer String Required
NotifyAddress String Required
RejectThresholdPct Int32 default 2
PACKAGE PARAMETERS (LoadSalesFact.dtsx)
BatchSize Int32 default 50000
RunMode String default "Full" -- Full | Quick
VARIABLES (package scope unless noted)
User::BatchId Int32 set by "Open batch" Execute SQL Task
User::RowsStaged Int32 Row Count
User::RowsRejected Int32 Row Count
User::ArchiveFolder String EvaluateAsExpression:
@[$Project::ArchiveRoot] + "\\" +
(DT_WSTR,4)YEAR(@[System::StartTime]) +
RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]),2) +
RIGHT("0" + (DT_WSTR,2)DAY(@[System::StartTime]),2)
User::CurrentFilePath String Foreach Loop scope, written by Variable Mappings
CONNECTION MANAGERS (project level)
StagingDb parameterised: CM.StagingDb.ServerName → $Project::StagingDbServer
CM.StagingDb.InitialCatalog → $Project::StagingDbCatalog
Warehouse parameterised: CM.Warehouse.ServerName → $Project::WarehouseServer
SourceFile Flat File, Expressions → ConnectionString = @[User::CurrentFilePath]
PROPERTY EXPRESSIONS
Foreach Loop Directory = @[$Project::InboundFolder]
File System Task Destination = @[User::ArchiveFolder]
Send Mail Task ToLine = @[$Project::NotifyAddress]
Send Mail Task Subject = "Sales load " + @[System::PackageName] +
" batch " + (DT_WSTR,12)@[User::BatchId]
"Full refresh" task Disable = @[$Package::RunMode] == "Quick"
PRECEDENCE CONSTRAINT (Expression and Constraint)
((DT_R8)@[User::RowsRejected] / ((DT_R8)@[User::RowsStaged] + 1.0)) * 100.0
> (DT_R8)@[$Project::RejectThresholdPct]
→ [Execute SQL Task] raise a reject alert

Then in SSISDB, three environments (Development, Test, Production) hold variables with identical names, and each project parameter is bound to the matching environment variable. Choosing the environment reference at execution time selects the whole configuration.

Note the details that make this maintainable:

  • Required on things that must not silently default — an unset production folder fails loudly rather than pointing at development.
  • A percentage threshold, not an absolute count, expressed with explicit DT_R8 casts because SSIS will not coerce.
  • + 1.0 in the denominator to avoid divide-by-zero when nothing was staged.
  • Disable as a property expression, giving a run-mode toggle without a second package.

Internal behavior: how values are resolved

Section titled “Internal behavior: how values are resolved”

It is tempting to picture this as a stack of four layers that overlay one another. That is the wrong model, and it produces exactly the wrong debugging instinct. The catalog stores one server-side assignment per parameter, in one of two modes.

catalog.object_parameters makes this explicit:

Column Meaning
design_default_value The default baked into the .ispac at build time
default_value The value currently in effect on the server — one slot
value_type V = that slot holds a literal; R = it holds a reference to an environment variable
referenced_variable_name When value_type = 'R', the environment variable name
value_set Whether a server-side assignment exists at all

So the resolution is:

1. DESIGN VALUE in the .ispac.
Used when the catalog has no assignment (value_set = 0).
2. CATALOG ASSIGNMENT catalog.set_object_parameter_value writes ONE slot,
in ONE of two mutually exclusive modes:
value_type = 'V' → a literal value
value_type = 'R' → a reference to an environment
variable, by name
Setting a literal REPLACES a reference, and vice
versa. They do not coexist and do not compete.
3. REFERENCE RESOLUTION only when value_type = 'R': the execution must name
an environment reference whose environment actually
contains that variable, or the execution fails
validation.
4. EXECUTION OVERRIDE catalog.set_execution_parameter_value applies to that
one execution only, above whatever step 2/3 produced.
catalog.execution_parameter_values is authoritative for what a given
execution actually received.

Why the distinction matters. “A stale server default is silently overriding my environment” is not a thing that can happen. If someone set a literal during an incident, that write replaced the environment reference — value_type flipped from R to V and referenced_variable_name went NULL. The parameter is no longer bound to the environment at all. That is a more serious and more findable failure than an override, and you diagnose it by looking at value_type, not by imagining a precedence race.

The failure cases are therefore distinct, and worth separating:

Symptom Actual state Check
Design value used unexpectedly No catalog assignment value_set = 0
A stale fixed value used Literal assignment value_type = 'V' where you expect 'R'
Binding “not working” Never bound as a reference value_type, referenced_variable_name
Reference exists but fails Execution named no/wrong environment reference catalog.environment_references, the execution’s reference_id
Reference exists, variable missing Environment lacks the variable catalog.environment_variables; validation reports it
One run differed from the rest Per-execution override catalog.execution_parameter_values.value_set

To see what a given execution actually used:

SELECT parameter_name,
parameter_value,
value_set, -- was it explicitly set for this execution?
sensitive
FROM catalog.execution_parameter_values
WHERE execution_id = @execution_id
ORDER BY object_type, parameter_name;
  • Variable with EvaluateAsExpression — on every read. A variable built from System::StartTime is stable across a run; one built from GETDATE() changes every time it is read, which is a subtle source of inconsistency.
  • Property expression — when the property is needed, which for connection managers means each acquisition. This is what makes the Foreach pattern work.
  • Precedence constraint expression — once, when the upstream executable completes.
  • Derived Column / Conditional Split expression — per row.

Parameters and expressions are resolved before validation. A connection string that resolves to an unreachable server fails at validation; one that resolves to an empty string usually fails with a confusing message about the connection manager, not about the parameter. This is why Required parameters are worth using — the failure names the parameter.

Before the project deployment model, environment-specific values came from package configurations, enabled per package and applied at load time.

Configuration type Stored in
XML configuration file A .dtsConfig file on disk
Environment variable A Windows environment variable
Registry entry The Windows registry
Parent package variable A variable in the calling package
SQL Server A configuration table in a database

The common bootstrap was indirect configuration: a Windows environment variable holds the path to the .dtsConfig, so the same package finds a different file on each machine.

Why it was replaced:

  • Configurations apply at load time and are order-dependent when chained; working out which one won is painful.
  • Values live outside the deployed artifact, with no validation that they exist or match.
  • Each package configures itself, so shared settings are duplicated across packages.
  • Failures are quiet by default — a missing configuration logs a warning and the package continues with design-time values. This is precisely how development connection strings reach production.

Package configurations remain supported, and packages in the package deployment model still use them. When you inherit a legacy estate, migrating configuration is usually the first modernisation step; the Project Conversion Wizard turns configurations into parameters mechanically, but the design cleanup afterwards is manual.

Expression length limits. The 4000-character cap on string expression results silently truncates. Symptom: dynamic SQL that runs but is missing its tail. Move construction into a Script Task (no cap) or, better, a stored procedure.

Variables of type Object. Hold recordsets from an Execute SQL Task with a full result set. They cannot be used in expressions, and their values are not saved in checkpoint files — so a restart loses them.

Parent package variable configuration. Under the package deployment model, a child reads a parent’s variable. Under the project model, use Execute Package Task parameter bindings instead — explicit, validated, and visible.

Casting in expressions changes precision. (DT_DECIMAL,2) truncates rather than rounds in some conversions. For money-like values, do arithmetic in SQL where the semantics are well-defined, and use expressions only for movement and formatting.

Environment variable name collisions. Two projects in one folder sharing an environment must agree on variable names. When they cannot, use separate environments — or absolute references to a shared one.

DelayValidation and expressions interact. A connection manager whose expression yields an invalid value at package start will fail validation unless the consuming task delays validation. Setting it on the wrong item is a common wasted hour.

Anti-pattern: configuration by package editing

Section titled “Anti-pattern: configuration by package editing”
BAD:
Deploy to test → open the package → change the server name → save → deploy
Deploy to prod → open the package → change the server name → save → deploy
Three different packages now exist. What was tested is not what shipped.
BETTER:
One .ispac, built once.
SSISDB environments Development / Test / Production supply the values.
Promotion = deploy the same artifact + select the environment reference.
BAD:
User::Config (String) = "server=...;db=...;folder=...;threshold=2"
...then SUBSTRING/FINDSTRING gymnastics to pull pieces out.

Parse-at-runtime configuration has no validation, no type safety, and fails obscurely. Use one parameter per value.

  • Using a variable where a parameter belongs. If the value comes from outside, it is a parameter.
  • Single backslashes in path expressions.
  • Building long dynamic SQL in an expression and hitting the 4000-character truncation.
  • Shadowed variable names across scopes.
  • Forgetting DelayValidation on tasks whose objects only exist at runtime.
  • Environments created by hand, so test and production drift invisibly.
  • Writing a literal onto a parameter during troubleshooting, which unbinds it from its environment variable and leaves it that way.
  • Marking nothing sensitive, so a password appears in a Verbose log.

Failure modes, recovery, and troubleshooting

Section titled “Failure modes, recovery, and troubleshooting”
Symptom Likely cause Check
Package uses the wrong server Parameter holds a literal (value_type = 'V'), so it is not bound to the environment at all catalog.object_parameters.value_type, then catalog.execution_parameter_values for that execution
“The connection is not found” at validation Connection manager expression resolved to empty Parameter has no value; make it Required
Path errors with odd characters Single backslash in an expression Double every backslash in string literals
Expression fails only at runtime Untested expression, or type mismatch Evaluate Expression button; check declared types
Dynamic SQL truncated 4000-character expression cap Move to Script Task or stored procedure
Value “not set” despite assignment Scope shadowing Variables window → Scope column
Works for one file, wrong file thereafter Connection manager not driven by property expression Connection manager → Expressions
Restart lost a recordset Object variables are not saved in checkpoints Re-derive it after restart
Password appears in a log Value passed through a non-sensitive variable Keep it in a sensitive parameter end to end

Diagnosis order for a configuration problem:

  1. Query catalog.execution_parameter_values for the failing execution — this shows what the package actually received, ending most arguments immediately.
  2. Check for server defaults on the project and package.
  3. Confirm the environment reference used, and that it is the environment you think.
  4. Only then look inside the package.

Configuration is not usually a performance topic, with three exceptions:

  • Variables with EvaluateAsExpression are recomputed on every read. A complex expression read once per row (for example in a Derived Column referencing such a variable) is real CPU cost. Compute once into a plain variable when the value is stable for the run.
  • RaiseChangedEvent = True fires an event on every assignment. On a variable written inside a loop this generates significant log volume.
  • Parameterised connection strings are resolved per acquisition. With RetainSameConnection = False and a task inside a tight loop, that is repeated connection setup.
  • ProtectionLevel = DontSaveSensitive plus sensitive parameters is the baseline for team work. The default (EncryptSensitiveWithUserKey) ties the package to one developer’s profile.
  • Prefer integrated authentication. The most secure secret is the one that does not exist.
  • Sensitive parameters are masked in catalog reports — but only while they stay sensitive. Copying into a normal variable removes the masking.
  • Configuration files in source control must not contain real credentials. Store server names (configuration); inject secrets from the CI system’s secret store.
  • Verbose logging records variable values. Treat it as a troubleshooting tool, not a default.
  • Environment permissions are separate from project permissions in SSISDB. Someone who can read an environment can read every non-sensitive value in it.

Testing, validation, and operational monitoring

Section titled “Testing, validation, and operational monitoring”
  • Validate after every deployment. catalog.validate_project catches a parameter with no value in this environment — the single most common deployment defect — before the schedule does.
  • Assert the environment. A cheap trick: a project parameter EnvironmentName, logged into the batch audit row at run start. If production ever logs “Development”, you find out immediately.
  • Diff environments between servers as part of release. Two environments with the same name and different variable sets is configuration drift, and it is invisible until it bites.
  • Alert on parameter values changing outside a deployment, if the catalog is shared with people who troubleshoot by editing server defaults.
Decision Option A Option B Choose A when
Connection config Parameterise ConnectionString whole Parameterise individual properties (CM.x.ServerName) You must compose an unusual provider string
Value source SSISDB environment Execution-time override from an orchestrator Configuration is stable per environment
Credentials Sensitive parameter Integrated authentication Integrated auth is genuinely unavailable
Dynamic behaviour Property expression Two packages, or a Disable toggle The variation is a value, not a shape
Legacy estate Migrate configurations to parameters Leave on package deployment model The packages are actively developed

Q: A package in production is connecting to the wrong database, but the environment variable is correct. Where do you look? Resist the urge to answer “precedence”. The catalog holds one server-side assignment per parameter, and catalog.object_parameters.value_type says which mode it is in: V for a literal, R for a reference to an environment variable. They are mutually exclusive — a literal does not sit “above” a reference and override it, it replaced it. So the real question is which of these is true: the parameter was never bound as R in the first place; it was bound and someone later wrote a literal, flipping it to V and clearing referenced_variable_name; the binding is intact but the execution named no environment reference or the wrong one; or a per-execution override was applied. Prove which by querying catalog.object_parameters for value_type/referenced_variable_name, and catalog.execution_parameter_values for what that execution actually received.

Q: Explain the difference between SSIS ISNULL and T-SQL ISNULL. T-SQL’s ISNULL(a, b) substitutes: it returns b when a is null. SSIS’s ISNULL(a) tests: it returns a Boolean. Substitution in SSIS is ISNULL(a) ? b : a, or REPLACENULL(a, b). Getting this wrong produces expressions that fail validation with a type error, or worse, expressions that silently propagate NULL through the pipeline.

Q: You need one package to process every file in a folder. What is the minimum wiring? A Foreach Loop with the Foreach File enumerator, its Directory set by a property expression from a parameter; a Variable Mapping writing index 0 to a variable; a Flat File connection manager whose ConnectionString is a property expression on that variable; and DelayValidation = True on the Data Flow Task, because the variable is empty at package start. The connection manager must also point at a real sample file at design time so column metadata exists.

Q: Why is Object a risky variable type? It cannot be used in expressions, it is the only way to carry a recordset (so it is often load-bearing), and its value is not persisted in checkpoint files — meaning a checkpoint restart loses it and any loop driven by it behaves differently on the second attempt. If a design depends on an Object variable surviving a restart, it does not.

Q: What is wrong with configuring environments by hand in SSMS? Drift. Environments created by clicking are not versioned, not diffable, and not reproducible; two servers acquire subtly different variable sets and nobody notices until a deployment behaves differently in production. Environment definitions belong in source control and should be applied by the deployment pipeline — see DevOps and Automation.

Q: A team wants to build a separate .ispac per environment because “the connection strings differ”. What is your response? That is not a build-time difference, it is a configuration difference, and separating builds breaks the guarantee that what was tested is what shipped. Parameterise the connection managers, hold values in SSISDB environments, and promote one artifact through the tiers. If something genuinely differs in shape rather than value, that is a design problem to solve with a Disable toggle or a run-mode parameter, not with divergent builds.

Q: How would you prove which configuration a failed production run actually used? catalog.execution_parameter_values for that execution_id. It lists every parameter, its value, whether it was explicitly set for the execution, and whether it is sensitive. Combined with catalog.executions (which records the environment reference used), that is a complete, auditable answer.