SSIS Fundamentals: Projects, Packages, Control Flow, and Data Flow
Why this topic matters
Section titled “Why this topic matters”Most SSIS problems that look mysterious — “it failed before doing anything”, “it works in Visual Studio but not from Agent”, “the package succeeded but loaded nothing” — are not really SSIS problems. They are the result of treating SSIS as one engine when it is two, and of not knowing what the runtime does between “start” and “the first row moves”.
This article builds the mental model everything else in this section depends on. If you read only one page here, read this one.
Fundamentals and terminology
Section titled “Fundamentals and terminology”SQL Server Integration Services is Microsoft’s data integration platform. It ships as part of SQL Server and consists of three separable pieces that people routinely conflate:
| Piece | What it is | Installed by |
|---|---|---|
| The SSIS runtime | The engine that executes packages, plus the Integration Services Windows service | SQL Server Setup → Integration Services |
| SSISDB (the catalog) | A database on the Database Engine that stores projects, parameters, environments, and execution history | Created manually in SSMS; requires CLR integration enabled |
| The designer | SQL Server Data Tools in Visual Studio plus the SQL Server Integration Services Projects extension | Visual Studio + Marketplace extension |
Installing Integration Services does not install the designer, and installing the designer does not give you a catalog. This trips up new environments constantly.
ETL and ELT
Section titled “ETL and ELT”| Approach | Where transformation happens | SSIS role |
|---|---|---|
| ETL | In the pipeline, between source and target | The data flow does the work: lookups, conversions, splits, aggregation |
| ELT | In the target database, after loading | SSIS lands raw data in staging; Execute SQL Tasks run set-based transforms |
Real packages mix the two. The design that scales best is E-L-T with a thin T in the pipeline: extract lean, do only what must happen row-by-row (parsing, cleansing, type conversion), bulk-load to staging, and let the database engine do joins and aggregation in set-based SQL. Large-Volume ETL and Performance Engineering explains why this usually wins.
The mental model: two engines, one file
Section titled “The mental model: two engines, one file”A package is not a script. It is a container for two different execution models that happen to be edited in the same window.
┌───────────────────────────────────────────────────────────────────┐│ PACKAGE (.dtsx) ││ ││ CONTROL FLOW — a workflow engine ││ • Runs tasks in an order you draw with precedence constraints ││ • Knows nothing about rows or columns ││ • Runs once per package execution ││ • This is where checkpoints, transactions, and loops live ││ ││ ┌──────────────┐ Success ┌──────────────┐ ││ │ Execute SQL │───────────>│ Data Flow │ ││ │ Truncate stg │ │ Task │ ││ └──────────────┘ └──────┬───────┘ ││ │ ││ DATA FLOW — a streaming pipeline engine│ ││ • Lives only inside a Data Flow Task │ ││ • Moves rows in memory buffers │ ││ • Components run concurrently, not in sequence ││ • No precedence constraints; rows are split, not branched ││ ▼ ││ ┌──────────┐ ┌────────────┐ ┌────────┐ ┌──────────────┐ ││ │ Flat File│─>│ Derived │─>│ Lookup │─>│ OLE DB │ ││ │ Source │ │ Column │ │ │ │ Destination │ ││ └──────────┘ └────────────┘ └───┬────┘ └──────────────┘ ││ │ no match ││ ▼ ││ ┌──────────────┐ ││ │ Reject │ ││ │ Destination │ ││ └──────────────┘ │└───────────────────────────────────────────────────────────────────┘Everything downstream follows from this split:
| Question | Control flow | Data flow |
|---|---|---|
| Unit of work | A task | A row buffer |
| Ordering | Explicit, via precedence constraints | Implicit; components run concurrently |
| Conditional branching | Precedence constraint expressions | Conditional Split (routes rows, not control) |
| Error semantics | Task fails → constraint decides what runs | Row errors → error output disposition decides |
| Restart granularity | Task or container (checkpoints) | None — a data flow restarts from row 1 |
| Variable writes | Free, at any point | Effectively at PostExecute for most purposes |
| Parallelism knob | MaxConcurrentExecutables |
EngineThreads, execution trees |
Projects and packages
Section titled “Projects and packages”Two units of organisation, and the difference decides how you deploy.
- Package — one unit of executable work, stored as a
.dtsxfile (XML). One control flow, plus any data flows inside it. - Project — a container for packages, project parameters, and project-level connection managers. Authored as
.dtproj, built into a single.ispac.
RetailAnalytics.dtproj Project (design time)├── Project.params Project parameters — shared by every package├── Connection Managers/ Project-level connection managers — shared│ ├── StagingDb.conmgr│ └── InboundFiles.conmgr├── LoadStoreDim.dtsx Package├── LoadProductDim.dtsx Package├── LoadSalesFact.dtsx Package└── MasterLoad.dtsx Orchestrates the others
↓ build
RetailAnalytics.ispac Single deployable artifactFile extensions
Section titled “File extensions”| Extension | What it is | Deployed? |
|---|---|---|
.dtsx |
A package — XML describing tasks, components, layout | Yes, inside the .ispac |
.dtproj |
The Visual Studio project file | No — design time only |
.ispac |
Built project deployment file | Yes — this is the deployment |
.dtsConfig |
Legacy XML package configuration | Package deployment model only |
.conmgr |
Project-level connection manager definition | Yes, inside the .ispac |
.params |
Project parameter definitions | Yes, inside the .ispac |
.dtsx is XML, so packages can be diffed — but the XML carries layout coordinates and regenerated GUIDs, so diffs are noisy and merges are unreliable. DevOps and Automation covers how teams work around that.
Deployment models
Section titled “Deployment models”| Project deployment model | Legacy package deployment model | |
|---|---|---|
| Unit of deployment | The project | Each package |
| Configuration | Parameters + SSISDB environments | Package configurations (.dtsConfig, registry, env var, SQL table) |
| Stored in | SSISDB catalog | File system or msdb |
| Logging | Captured automatically by the catalog | Requires a log provider per package |
| Validation | Server-side, before execution | At execution time |
| Requires CLR | Yes | No |
The project model has been the default since SQL Server 2012. The package model remains supported for backward compatibility — you will meet it in inherited estates and in some Azure scenarios that deliberately keep packages on a file share. New work should use the project model.
Minimal working example
Section titled “Minimal working example”The smallest package that does something real: read a CSV, load it to a table.
Control flow: [Execute SQL Task: TRUNCATE TABLE stg.Store] │ Success ▼ [Data Flow Task: Load stores]
Data flow inside that task: Flat File Source (stores.csv) ──> OLE DB Destination (stg.Store, fast load)-- The staging table the package targetsCREATE TABLE stg.Store( StoreCode VARCHAR(10) NOT NULL, StoreName VARCHAR(100) NOT NULL, Region VARCHAR(50) NULL, OpenedOn DATE NULL, LoadedOn DATETIME2(3) NOT NULL CONSTRAINT DF_stgStore_LoadedOn DEFAULT SYSUTCDATETIME());Three things are already worth noticing, because they scale into every later article:
- The truncate is an Execute SQL Task in the control flow, not something the data flow does. Set-based work belongs in SQL.
- The destination uses fast load, not the default row-by-row insert.
- The staging table is a heap with no indexes, because it is a landing zone, not a query target.
Practical implementation: connection managers
Section titled “Practical implementation: connection managers”Connection managers are named, reusable connection definitions. Tasks and components reference them by name rather than embedding connection details, which is what makes a package portable between environments.
| Scope | Visible to | Use for |
|---|---|---|
| Package-level | One package | Connections genuinely specific to that package |
| Project-level | Every package in the project | Shared targets: the staging database, the inbound file root |
Common types: OLE DB, ADO.NET, Flat File, Excel, File, FTP, SMTP, ODBC, Cache.
Choosing between OLE DB and ADO.NET for SQL Server:
| OLE DB | ADO.NET | |
|---|---|---|
| Throughput | Generally higher; supports fast load | Lower; row-by-row insert by default |
| Parameter syntax | Positional ? |
Named @Param |
| Typing | SSIS types via the provider | .NET types, fewer conversion surprises |
| Use when | Bulk-loading SQL Server | A component needs .NET typing, or no OLE DB provider exists |
Never hard-code a connection string per environment. Parameterise it so one build runs in development, test, and production unchanged — see Variables, Parameters, Expressions, Connection Managers, and Configuration.
Variables and parameters, briefly
Section titled “Variables and parameters, briefly”- Variables are the package’s working memory — row counts, the current file name, computed paths. They change during execution.
- Parameters are the package’s inputs — connection details, batch sizes, run-mode flags. They are fixed for an execution and supplied from outside.
The rule: if the value comes from outside the package, it is a parameter. That article covers scope, expressions, and how a catalog parameter value actually resolves — including why writing a literal replaces an environment binding rather than overriding it.
A realistic end-to-end example
Section titled “A realistic end-to-end example”A fictional retail analytics load. Each night, an inbound folder receives one CSV per store region, and a warehouse fact table must be updated.
MasterLoad.dtsx (control flow)
[Execute SQL Task] Open batch: INSERT dbo.EtlBatch, capture BatchId │ Success ▼ [Sequence Container: "Dimensions"] TransactionOption = Supported ├── [Execute Package Task] LoadStoreDim.dtsx └── [Execute Package Task] LoadProductDim.dtsx (may run concurrently) │ Success (Logical AND — both must finish) ▼ [Foreach Loop Container] Foreach File over @[$Project::InboundFolder], *.csv │ Variable mapping: index 0 → User::CurrentFilePath │ ├── [Data Flow Task] Load one region file → stg.SalesLine │ DelayValidation = True (path is empty until runtime) │ ├── [Success] [File System Task] Move file → Archive\yyyyMMdd\ └── [Failure] [File System Task] Move file → Quarantine\ │ Success ▼ [Execute SQL Task] Apply stg.SalesLine → dbo.FactSales (set-based, idempotent) │ Success ▼ [Execute SQL Task] Close batch: rows loaded, rows rejected, status = 'Success'
Event handlers: OnError → INSERT dbo.EtlErrorLog (records every error) OnTaskFailed → close batch as 'Failed' (fires once per failed task)The data flow inside the Foreach Loop:
Flat File Source (SalesFeed) -- explicit column widths, FastParse on numerics │ ▼Derived Column -- UPPER(TRIM([Region])), (DT_DECIMAL,4)[DiscountPct]/100, │ (DT_DBTIMESTAMP)@[System::StartTime], BatchId ▼Conditional Split ├── "Invalid" : ISNULL([StoreCode]) || ISNULL([SaleDate]) ──> Reject destination └── default ▼Lookup (Full Cache, SELECT StoreCode, StoreKey FROM dbo.DimStore) ├── no-match output ──> Reject destination (unknown store) └── match output ▼OLE DB Destination (fast load) → stg.SalesLineWhy it is shaped this way:
- Dimensions before facts, made explicit with a constraint rather than left to timing.
- One Data Flow Task per file, so a failure quarantines one file rather than the batch.
- Rejects are routed, not fatal — one malformed row does not kill a million-row load.
- The apply is one set-based statement, not row-by-row updates from the pipeline.
- The batch table is what operations reads; catalog logging tells you about packages, not about business outcomes.
Internal execution behavior: the lifecycle
Section titled “Internal execution behavior: the lifecycle”This is the part that explains “it failed before doing anything”.
1. LOAD Runtime reads the .dtsx, applies the ProtectionLevel, decrypts sensitive properties. ↓ Failure here: "Failed to decrypt protected XML node" — wrong user key or password.
2. PARAMETER BINDING / CONFIGURATION Project model: each parameter resolved from its single catalog assignment (a literal, or an environment-variable reference resolved via the execution's environment reference), plus any per-execution override. Package model: package configurations applied, in configured order. ↓ Failure here is often silent in the package model — a missing .dtsConfig logs a warning and the package runs with design-time values.
3. VALIDATION Every task and every data flow component verifies what it needs exists: tables, columns, files, expected metadata. ↓ This is where VS_NEEDSNEWMETADATA and "column cannot be found at the datasource" appear — before a single row moves.
4. PRE-EXECUTE Components acquire connections and allocate buffers. Lookups in Full Cache mode populate here — this can take a long time.
5. EXECUTE Control flow: tasks run per precedence constraints, in parallel where allowed. Data flow: sources fill buffers; components process them concurrently.
6. POST-EXECUTE Components release resources, write final row counts, set output variables. Script Component variable writes land here.
7. CLEANUP Connections close; the checkpoint file is deleted on success; the final execution result is reported to the caller.Validation, and the two properties that change it
Section titled “Validation, and the two properties that change it”Validation is a feature, not an obstacle: it turns a runtime failure at 02:40 into a design-time red X. But some designs legitimately reference objects that do not exist yet.
| Property | Level | Effect |
|---|---|---|
DelayValidation |
Task / container / package | Defers validation until immediately before the item runs |
ValidateExternalMetadata |
Data flow component | Skips checking the external object’s shape at validation time |
Set DelayValidation = True when:
- A staging table is created earlier in the same control flow.
- A file path comes from a variable that is empty at package start (the Foreach Loop pattern above).
- A connection string is built at runtime.
Set ValidateExternalMetadata = False on the specific component that references an object not present at design time — not as a blanket habit.
Both trade an early, clear failure for a late, expensive one. Use them where the design requires it, and nowhere else.
Metadata is fixed at design time
Section titled “Metadata is fixed at design time”The data flow’s column metadata — names, types, lengths, and internal lineage IDs — is baked into the .dtsx. The engine allocates buffers from it before reading any data.
Consequences that surprise people:
- Changing a source column’s type means re-opening every downstream component to accept new metadata. This is the
VS_NEEDSNEWMETADATAcascade. - A “dynamic” data flow whose columns change per iteration is not possible with built-in components. Metadata-driven loads must either keep one shape for all iterations or use a generic staging approach.
- Error outputs report
ErrorColumnas a lineage ID, not a name — which is why raw reject files are unreadable until you translate it. Error Handling shows how.
Where execution actually happens
Section titled “Where execution actually happens”A persistent misunderstanding: connecting to a remote SQL Server does not run the package there. The package runs wherever the process was started — your workstation during debugging, the Agent server for a scheduled job, the Azure-SSIS IR node in the cloud.
This decides:
- Which machine’s memory the buffers consume.
- Which machine’s file system a “local” path refers to.
- Which account’s credentials reach a file share.
- Whether SSIS and the Database Engine are competing for RAM on the same box.
“It worked in Visual Studio” almost always reduces to one of these.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”32-bit vs 64-bit. Some legacy providers (notably the older Jet/ACE drivers for Excel and Access) exist only in 32-bit. Visual Studio’s designer runs 32-bit, so a package can debug fine and fail under the 64-bit runtime. The catalog exposes a Use 32-bit runtime execution option, and dtexec has a 32-bit build, for exactly this.
TargetServerVersion. A project property that decides which SSIS version the packages are built for. A package built for a newer version will not run on an older runtime. Keep it aligned with the server you deploy to, and remember that opening an older project in a newer designer may offer to upgrade it — one-way.
ProtectionLevel. Controls how sensitive properties are stored in the .dtsx:
| Value | Behaviour | Suitable for |
|---|---|---|
EncryptSensitiveWithUserKey |
Encrypted with the developer’s Windows profile key. The default | Nothing beyond a single developer’s machine |
EncryptSensitiveWithPassword |
Encrypted with a password supplied at load | Rare; complicates automated builds |
EncryptAllWithPassword |
Whole package encrypted | Rare; blocks diffing entirely |
DontSaveSensitive |
Sensitive values simply not stored | The right default for team work — supply values via parameters |
ServerStorage |
Protection delegated to SSISDB | Catalog deployments |
The default (EncryptSensitiveWithUserKey) is the cause of the classic “works for me, fails for everyone else” package. Use DontSaveSensitive and pass credentials as sensitive parameters — or better, use integrated authentication so there is no secret at all.
Package “success” with zero rows. A data flow that reads an empty file, or whose source query returns nothing, succeeds. If loading nothing is a business failure, you must assert it — a Row Count into a variable plus a precedence constraint expression.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: the one-package monolith
Section titled “Anti-pattern: the one-package monolith”BAD: DailyLoad.dtsx 40 tasks, 12 data flows, 3 nested Foreach loops, every dimension and fact in one control flowWhy it fails:
- Cannot be restarted partway — a failure at task 38 re-runs everything.
- Cannot be tested in pieces.
- Cannot be worked on by two people, because
.dtsxmerges badly. - Parallelism is invisible and unmanaged.
- Debugging means running the whole thing.
BETTER: MasterLoad.dtsx ├── Execute Package Task → LoadStoreDim.dtsx ├── Execute Package Task → LoadProductDim.dtsx └── Execute Package Task → LoadSalesFact.dtsxEach child is independently runnable, testable, restartable, and editable.
Other frequent mistakes
Section titled “Other frequent mistakes”- Treating the data flow as a sequence of steps. Components do not finish one after another; they process buffers concurrently.
- Joins and aggregation in the pipeline when the source database could do them in SQL.
SELECT *in sources — wide buffers, wasted memory, and metadata churn on every source change.DelayValidation = Trueeverywhere, converting fast design-time errors into slow runtime ones.- Leaving
ProtectionLevelat the default and wondering why the build server cannot open the package. - Hard-coded paths and server names, making the same build undeployable across environments.
Failure modes, recovery, and troubleshooting
Section titled “Failure modes, recovery, and troubleshooting”| Symptom | Usual cause | Where to look |
|---|---|---|
| Fails immediately, no rows processed | Validation | The first error message names the component and object |
VS_NEEDSNEWMETADATA |
Source columns changed since design time | Re-open the affected components; check for SELECT * |
| “Failed to decrypt protected XML node” | EncryptSensitiveWithUserKey under a different account |
Change ProtectionLevel to DontSaveSensitive |
| Works in VS, fails from Agent | Different account, bitness, or working directory | Check the proxy account, the 32-bit option, and absolute paths |
| Succeeds but loads nothing | Empty source, or a filter excluding everything | Row Count + assertion; check the source query’s parameters |
| Cannot find file at runtime | Relative path resolved against the executing process’s directory | Use full paths built from parameters |
| Hangs at pre-execute | A Full Cache Lookup loading a huge reference set | Restrict the lookup query, or change cache mode |
A repeatable triage order:
- Read the first error, not the last. SSIS reports a cascade; the first message names the real component.
- Establish which phase it failed in — validation, pre-execute, or execute. That halves the search space immediately.
- Confirm which machine and account it ran under.
- Reproduce with the same parameter values, from the catalog, not from Visual Studio.
- Raise the logging level to
Verbosefor one execution to get per-component row counts.
Performance and resource considerations
Section titled “Performance and resource considerations”At fundamentals level, three facts govern everything:
- Row width decides buffer capacity. SSIS sizes buffers from declared column types, not actual data. A
VARCHAR(4000)column holding 20 characters still reserves 4000 bytes per row. - Blocking transformations turn a stream into a batch.
SortandAggregatemust consume the entire input before emitting anything. - SSIS and SQL Server compete for memory when they share a machine.
The full treatment is in Large-Volume ETL and Performance Engineering.
Security and configuration considerations
Section titled “Security and configuration considerations”- Two identities, not one. The deployment identity writes projects and environments to SSISDB. The execution identity (Agent service account or proxy) runs packages and needs access to files, shares, and source systems. Grant each only what it needs.
ProtectionLevel = DontSaveSensitiveplus catalog parameters keeps secrets out of the.dtsx.- Prefer integrated authentication so there is no stored credential to rotate.
- Checkpoint and log files are not covered by the package protection level. They contain variable values; secure the folders with an ACL.
- Sensitive parameters are encrypted by the SSISDB database master key — which is why the catalog password matters at restore time.
Testing, validation, and operational monitoring
Section titled “Testing, validation, and operational monitoring”SSIS has no meaningful unit test framework, so verification is layered:
| Layer | Verifies | How |
|---|---|---|
| Build | The project compiles, components resolve | The build step |
| Catalog validation | Parameters, connections, environment references resolve on this server | catalog.validate_project |
| Smoke run | The package runs end to end on known input | Execute against a seeded database |
| Data assertions | It produced the right answer | SQL checks: row counts, control totals, referential integrity, reject counts |
Design for testability from the start: parameterised paths and connections, staging tables you can inspect, and an idempotent apply you can run twice.
Trade-offs and alternatives: when SSIS fits
Section titled “Trade-offs and alternatives: when SSIS fits”SSIS is genuinely good at:
- Bulk-loading large flat files into SQL Server.
- File-driven workflows — poll, iterate, archive, quarantine.
- Heterogeneous sources into one target.
- Data warehouse dimension and fact loads.
- Orchestration on Windows where SQL Server Agent already exists.
Choose SSIS when the data is on-premises or in SQL Server, the team already runs Agent, volumes are large but bounded (batch, not stream), and the logic is expressible as SQL plus modest row-level work.
Consider something else when the workload is cloud-native or SaaS-API-driven, you need streaming rather than batch windows, the logic is complex enough to deserve real unit tests, or the target is a cloud warehouse whose own bulk-load path beats pushing rows through a pipeline.
Modernizing SSIS Workloads turns this into a per-workload decision guide.
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”Q: Explain the difference between control flow and data flow, and give one consequence for error handling.
Control flow is a workflow engine ordering tasks via precedence constraints; data flow is a streaming pipeline moving row buffers inside a single Data Flow Task. Consequence: a control flow failure is governed by FailPackageOnFailure, MaximumErrorCount, and the Failure constraint; a row failure is governed by the component’s error output disposition (Fail Component / Ignore Failure / Redirect Row). A red arrow cannot catch a bad row, and an error output cannot catch a failed Execute SQL Task.
Q: A package fails instantly with no rows processed. Walk me through your diagnosis.
Almost certainly validation. Read the first error; identify the component and the object it could not resolve; determine whether the object legitimately does not exist yet (create-then-use in the same control flow, or a variable-driven path) — in which case DelayValidation on the task or ValidateExternalMetadata = False on the component is the fix. If the object should exist, the problem is the connection, the account, or a schema change.
Q: Why does a package that works in Visual Studio fail under SQL Server Agent?
Different execution context. Candidates in order: the account (Agent service account or proxy lacks file-share or database rights), bitness (designer is 32-bit; the Agent step defaults to 64-bit, breaking legacy providers), ProtectionLevel (EncryptSensitiveWithUserKey cannot be decrypted by another account), relative paths resolving against a different working directory, and mapped drives that do not exist in a service session.
Q: What is VS_NEEDSNEWMETADATA and what does it tell you about the engine?
It means a data flow component’s cached column metadata no longer matches the external object. It tells you metadata is fixed at design time and stored in the package, that buffers are sized from it before execution, and therefore that data flows cannot adapt to changing shapes at runtime.
Q: When would you deliberately set DelayValidation = True, and what does it cost?
When an object referenced by a task does not exist at package start — a table created earlier in the same control flow, or a file path supplied by a variable. The cost is losing an early, cheap, clear failure: the error now surfaces mid-run, potentially after other work has already committed.
Q: You inherit a 40-task single package that fails intermittently at different points. What is your first structural change?
Split it into child packages orchestrated by a master. That immediately buys independent restart, independent testing, parallelism you can see and control, and the ability for more than one person to work on it — because .dtsx files merge badly.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Control Flow: Tasks, Containers, and Precedence Constraints
- SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
- SSIS Variables, Parameters, Expressions, Connection Managers, and Configuration
- SSIS Deployment, SSISDB, Environments, and Operations
- SQL Server Indexes — destination indexing decides much of your load throughput
- SQL Server Data Types — declared width drives buffer capacity
- Database Terms Glossary