SSIS Variables, Parameters, Expressions, Connection Managers, and Configuration
Why this topic matters
Section titled “Why this topic matters”“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.
Fundamentals and terminology
Section titled “Fundamentals and terminology”Variables vs parameters
Section titled “Variables vs parameters”| 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.
Variables
Section titled “Variables”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— setTrueand 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.
Parameters: project vs package
Section titled “Parameters: project vs package”- 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 SSISRead that top to bottom and most configuration questions answer themselves: environments feed parameters; parameters and variables feed expressions; expressions rewrite properties.
Minimal working example
Section titled “Minimal working example”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.
Scope, and the bug it causes
Section titled “Scope, and the bug it causes”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 sequenceShadowing 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 0Practical 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.
Expressions in depth
Section titled “Expressions in depth”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.
Syntax essentials
Section titled “Syntax essentials”"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 / notcond ? 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 scaleFunctions you will reach for
Section titled “Functions you will reach for”| 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 |
The differences that cause bugs
Section titled “The differences that cause bugs”ISNULLtests, it does not substitute. T-SQL:ISNULL(a, b)returnsbwhenais null. SSIS:ISNULL(a)returns a Boolean. Substitution isISNULL(a) ? b : a— orREPLACENULL(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_WSTRtoDT_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, not1/0. - Integer division truncates.
(DT_I4)7 / (DT_I4)2is3. Cast to a decimal type first. - Date arithmetic returns
DT_DBTIMESTAMP; comparing it to aDT_DBDATEcolumn needs an explicit cast.
Test before you deploy
Section titled “Test before you deploy”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.
Property expressions
Section titled “Property expressions”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.
The canonical dynamic-file pattern
Section titled “The canonical dynamic-file pattern”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 = Trueon the Data Flow Task, because at package start the variable is empty and validation against a non-existent path would fail.
Connection managers and parameterisation
Section titled “Connection managers and parameterisation”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::StagingDbCatalogPrefer 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.
Sensitive values
Section titled “Sensitive values”- Mark credential parameters
Sensitive— encrypted in the catalog, masked in reports. - Set the package
ProtectionLevel = DontSaveSensitiveso nothing secret is stored in the.dtsxat 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 realistic end-to-end example
Section titled “A realistic end-to-end example”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 alertThen 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:
Requiredon 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_R8casts because SSIS will not coerce. + 1.0in the denominator to avoid divide-by-zero when nothing was staged.Disableas 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”How a parameter value is resolved
Section titled “How a parameter value is 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? sensitiveFROM catalog.execution_parameter_valuesWHERE execution_id = @execution_idORDER BY object_type, parameter_name;When expressions are evaluated
Section titled “When expressions are evaluated”- Variable with
EvaluateAsExpression— on every read. A variable built fromSystem::StartTimeis stable across a run; one built fromGETDATE()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.
Validation interaction
Section titled “Validation interaction”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.
Legacy package configurations
Section titled “Legacy package configurations”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.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”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.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”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.Anti-pattern: the god-variable
Section titled “Anti-pattern: the god-variable”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.
Other frequent mistakes
Section titled “Other frequent mistakes”- 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
DelayValidationon 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
Verboselog.
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:
- Query
catalog.execution_parameter_valuesfor the failing execution — this shows what the package actually received, ending most arguments immediately. - Check for server defaults on the project and package.
- Confirm the environment reference used, and that it is the environment you think.
- Only then look inside the package.
Performance and resource considerations
Section titled “Performance and resource considerations”Configuration is not usually a performance topic, with three exceptions:
- Variables with
EvaluateAsExpressionare 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 = Truefires 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 = Falseand a task inside a tight loop, that is repeated connection setup.
Security and configuration considerations
Section titled “Security and configuration considerations”ProtectionLevel = DontSaveSensitiveplus 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.
Verboselogging 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_projectcatches 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.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| 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 |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”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.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Fundamentals
- SSIS Control Flow: Tasks, Containers, and Precedence Constraints
- SSIS Deployment, SSISDB, Environments, and Operations
- SSIS DevOps and Automation
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
Official Microsoft references
Section titled “Official Microsoft references”- Integration Services (SSIS) Variables
- Integration Services (SSIS) Package and Project Parameters
- Integration Services (SSIS) Expressions
- Use Property Expressions in Packages
- Integration Services (SSIS) Connections
- Legacy Package Deployment (SSIS)
- catalog.object_parameters (SSISDB Database)
- catalog.set_object_parameter_value (SSISDB Database)
- catalog.set_execution_parameter_value (SSISDB Database)
- catalog.execution_parameter_values (SSISDB Database)