SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
Why this topic matters
Section titled “Why this topic matters”The data flow is where SSIS earns its reputation, in both directions. A well-shaped pipeline streams tens of millions of rows on modest hardware with flat memory use. A badly shaped one — the same components, arranged differently — runs out of memory on a fraction of that volume, or spends an hour doing what SQL would do in four minutes.
The difference is almost never the component list. It is whether the designer understood buffers, blocking behaviour, and where work belongs. This article builds that understanding from the engine outwards.
Fundamentals and terminology
Section titled “Fundamentals and terminology”A data flow lives inside a Data Flow Task and has three kinds of component:
| Component kind | Role | Examples |
|---|---|---|
| Source | Reads external data into the pipeline | OLE DB Source, Flat File Source, ADO.NET Source |
| Transformation | Modifies, routes, combines, or aggregates rows | Derived Column, Lookup, Conditional Split, Sort |
| Destination | Writes rows out | OLE DB Destination, Flat File Destination |
Components are connected by paths. A path carries rows and — importantly — carries metadata: the column names, types, lengths, and lineage IDs that the engine uses to lay out memory.
Some components also expose an error output: a second path carrying rows that failed, plus ErrorCode and ErrorColumn.
The mental model: buffers, not rows
Section titled “The mental model: buffers, not rows”The engine does not move rows one at a time. It fills fixed-size blocks of memory called buffers, and passes buffer references between components.
┌─────────────────────────────────────────────┐Source ─┤ Buffer #1 [row][row][row]…[row] ├─> the SAME memory is └─────────────────────────────────────────────┘ handed downstream
Derived Column writes into spare columns of that same buffer → no copyConditional Split marks which output each row belongs to → no copyLookup adds matched columns into the same buffer → no copy
┌─────────────────────────────────────────────┐Sort ───┤ must read EVERY buffer before emitting any ├─> NEW buffers allocated, └─────────────────────────────────────────────┘ whole set held in memoryThis single idea explains nearly every performance property of SSIS:
- A synchronous output reuses the input’s buffer. Each row is processed independently of the others and passed on as it is handled, so the engine does not allocate a new buffer for that output. In the object model this is expressed by setting the output’s
SynchronousInputIDto the ID of the component’s input. - An asynchronous output does not. The component cannot pass each row along as it is processed — it must accumulate rows, combine inputs, or emit a different set of rows entirely — so the engine creates a new output buffer and starts a new execution tree. In the object model,
SynchronousInputIDis0.
Careful: synchronous does not mean “one row in, one row out”. That shortcut is wrong, and the article’s own component list would contradict it. A synchronous component can route rows among several outputs and can drop them from the default path entirely:
- Conditional Split is synchronous. It uses an
ExclusionGroupand callsDirectRowto send each row to exactly one of its outputs. Every output shares the input’s buffer; none of them individually has the same row count as the input. - Error outputs work the same way, via
DirectErrorRow. A redirected row leaves the main path without any new buffer being allocated.
So the test is buffer lineage and row independence, not cardinality. Microsoft’s criteria for needing an asynchronous output are that the component must gather multiple buffers before it can act (Sort), must combine rows from multiple inputs (Merge), or has no one-to-one correspondence between input and output rows (Aggregate, which adds rows to hold computed values). Cardinality appears there as one reason a component must be asynchronous — not as the definition of synchronous.
Blocking is a third, related-but-separate axis. It describes when a component can emit rows and how much upstream state it must hold meanwhile. All fully blocking components are asynchronous, but not all asynchronous components are fully blocking — Union All allocates new buffers yet still streams.
| Behaviour | Buffer | What it means | Examples |
|---|---|---|---|
| Non-blocking | Synchronous — input buffer reused | Rows are passed on as they are processed; memory stays flat | Derived Column, Data Conversion, Conditional Split, Multicast, Lookup (after cache warm-up), Row Count, Audit, Character Map |
| Partially blocking | Asynchronous — new output buffer | Holds some rows while waiting for matches, but still streams output | Merge, Merge Join, Union All, Pivot, Unpivot |
| Fully blocking | Asynchronous — new output buffer | Consumes the entire input before emitting one row | Sort, Aggregate, Fuzzy Lookup, Fuzzy Grouping |
One Sort in the middle of a large pipeline is the most common cause of an SSIS package that “used to be fine”. It converts a streaming design into a batch one, and every row must fit in memory or spill to disk.
The practical rule that follows: sort and aggregate in the source query, not in the pipeline. The database has indexes and a query optimiser; the pipeline has neither.
Minimal working example
Section titled “Minimal working example”Flat File Source (stores.csv) ──> OLE DB Destination (stg.Store, fast load)Two components, no transformations, and already two decisions that matter:
- The Flat File Source’s columns are declared with real widths and types, not the default
DT_STR(50). - The destination uses Table or view — fast load, not the default
Table or view, which issues oneINSERTper row.
Everything else in this article is elaboration on those two themes: keep rows narrow, and use the bulk path.
Sources
Section titled “Sources”| Source | Notes |
|---|---|
| OLE DB Source | The default for SQL Server. Use SQL command, not Table or view, so you control columns and predicates |
| ADO.NET Source | For providers with no OLE DB equivalent, or when .NET typing avoids conversion pain. Generally lower throughput for SQL Server |
| Flat File Source | Delimited or fixed-width text. Declare types and lengths deliberately |
| Excel Source | Convenient, fragile: the driver infers types by sampling rows, so a mostly-numeric column with occasional text is a recurring problem |
| XML Source | Reads XML with inline or referenced XSD. Produces one output per repeating element, which you then rejoin |
| Raw File Source | SSIS’s own binary format — fast, no conversion, ideal for handing data between packages |
| ODBC Source | Sources with only ODBC drivers |
Access modes, and why “SQL command” wins
Section titled “Access modes, and why “SQL command” wins”| Access mode | Behaviour |
|---|---|
| Table or view | SELECT * against the object |
| Table name or view name variable | Same, with the name from a variable |
| SQL command | Your query — columns, predicates, joins, hints |
| SQL command from variable | Your query, built at runtime |
-- Prefer thisSELECT StoreCode, StoreName, Region, OpenedOnFROM dbo.StoreWHERE ModifiedOn > ?;-- Not this — every column travels through every buffer,-- and adding a column upstream breaks the package's metadataSELECT * FROM dbo.Store;Narrower rows mean more rows per buffer, fewer buffers, and less memory. Remember that buffer cost is driven by declared type, not actual content: a VARCHAR(4000) column holding 20 characters still reserves 4000 bytes per row.
Filtering belongs in the source too. A WHERE clause using an index beats reading everything and discarding rows in a Conditional Split.
Flat file specifics
Section titled “Flat file specifics”- Declare column widths explicitly. The default
DT_STR(50)both truncates real data and wastes buffer space. - Choose the right code page, and tick Unicode for UTF-16. UTF-8 generally needs code page 65001 with careful type mapping.
FastParse— an advanced per-column setting on integer, date, and time columns. Skips locale-aware parsing; faster, but only accepts invariant formats.- Ragged rows. An unquoted delimiter inside a field shifts every subsequent column. Configure error output redirection so the row is quarantined rather than fatal.
- Fixed-width and ragged-right layouts parse more cheaply than delimited-with-quotes, when you control the format.
Destinations
Section titled “Destinations”| Destination | Notes |
|---|---|
| OLE DB Destination | The standard SQL Server target. Use Table or view — fast load |
| SQL Server Destination | Bulk-load destination that requires the package to run on the same machine as the target instance — inflexible; most teams use OLE DB fast load |
| ADO.NET Destination | Row-by-row inserts by default; for providers without OLE DB support |
| Flat File Destination | Extracts and reject files |
| Excel Destination | Small hand-offs only; same driver typing issues |
| Raw File Destination | Intermediate storage between packages, no conversion cost |
| OLE DB Command | Not a destination, but frequently misused as one — see below |
Fast load and its options
Section titled “Fast load and its options”Table or view issues singleton INSERTs. Table or view — fast load uses the bulk insert path. On a large load the difference is not marginal.
| Option | Effect | Consider |
|---|---|---|
Table lock (TABLOCK) |
Bulk update lock on the table | Faster; blocks other writers and prevents concurrent loads into the same table |
| Check constraints | Constraints enforced during load | Leave on unless data is validated elsewhere; off is faster and admits bad rows |
| Rows per batch | Row-count hint to the optimiser | Helps plan choice for the insert |
| Maximum insert commit size | Rows committed per transaction | The setting with the biggest blast radius — see below |
| Keep identity | Preserve source identity values | Needed when the key is meaningful |
| Keep nulls | Preserve NULLs rather than applying column defaults | Changes semantics — decide deliberately |
Advanced options available by typing into FastLoadOptions in the Advanced Editor include KILOBYTES_PER_BATCH, FIRE_TRIGGERS (triggers do not fire by default under fast load), and ORDER (declaring the input’s sort order, which can help when it matches the clustered index).
Commit size deserves real thought. Too large means one enormous transaction — log growth, memory pressure, and a long rollback on failure. Too small means constant commit overhead. Tens of thousands to low hundreds of thousands of rows is a sane starting range, then measure.
Two consequences that are easy to miss:
- A constraint violation fails the whole batch defined by the commit size, not just the offending row. Batch sizing is an error-handling decision as much as a performance one.
- The default differs by SSIS version and access mode — Microsoft’s own documentation shows both
0and2147483647on different pages. Read the actual value in your destination rather than assuming.
Anti-pattern: OLE DB Command as an update path
Section titled “Anti-pattern: OLE DB Command as an update path”The OLE DB Command transformation runs a parameterised statement once per row.
BAD: Source ──> Lookup ──> OLE DB Command "UPDATE dbo.Customer SET Region = ? WHERE CustomerId = ?"
100,000 changed rows = 100,000 round trips.BETTER: Source ──> Lookup ──> OLE DB Destination (fast load) ──> stg.CustomerChange then, in the control flow: [Execute SQL Task] one set-based UPDATE ... FROM stg.CustomerChangeOLE DB Command is defensible for genuinely small row counts, or when each row must invoke a stored procedure with side effects. It is not defensible as the standard way to apply updates.
Core transformations
Section titled “Core transformations”Derived Column
Section titled “Derived Column”Adds or replaces columns using SSIS expressions — the everyday cleansing tool.
UPPER(TRIM([Region])) -- normalise(DT_DECIMAL,4)[DiscountPercent] / 100 -- 0–100 → 0–1ISNULL([Channel]) ? "UNKNOWN" : [Channel] -- default a missing value(DT_DBTIMESTAMP)@[System::StartTime] -- audit column[OrderTotal] > 10000 ? (DT_I4)1 : (DT_I4)0 -- flag(DT_WSTR,50)@[User::BatchId] -- stamp the batchExpression traps:
- Conditional is C-style
cond ? a : b, not T-SQLCASE. - String literals use double quotes;
==compares,=does not exist. ISNULL()tests, it does not substitute. T-SQL’sISNULL(a,b)is SSIS’sISNULL(a) ? b : a.- NULL propagates: any expression touching NULL returns NULL unless handled.
- Casts are explicit and mandatory:
(DT_I4),(DT_STR,50,1252),(DT_WSTR,50),(DT_DECIMAL,2). - Expression string results are capped at 4000 characters — long dynamic SQL gets silently truncated. Build it in a Script Component or a stored procedure.
Replace vs add. Replacing an existing column reuses its buffer slot; adding a new one widens every row. When you are converting a value, prefer replacing in place.
Data Conversion
Section titled “Data Conversion”Converts a column to a different SSIS type, producing a new column — so both the original and the converted column travel downstream, widening the buffer.
Better: convert at the source. Cast in the SQL query, or declare the correct type in the Flat File connection manager. Use Data Conversion only when the source genuinely cannot produce the target type — most often DT_STR from a flat file into an NVARCHAR column expecting DT_WSTR.
Conditional Split
Section titled “Conditional Split”Routes each row to one of several outputs by expression, evaluated in order; first match wins; unmatched rows go to the default output.
Output "Rejects" : ISNULL([StoreCode]) || TRIM([StoreName]) == ""Output "HighValue" : [OrderTotal] >= 10000Output "Standard" : [OrderTotal] > 0Default output : everything elseConnect the default output, even if only to a Row Count. Rows that match nothing take it silently, and if it is unconnected they vanish without trace.
Lookup
Section titled “Lookup”Joins the pipeline against a reference set — the standard business-key-to-surrogate-key step, and the standard “does this row already exist?” test.
| Cache mode | Behaviour | Use when |
|---|---|---|
| Full Cache | Entire reference set loaded into memory during pre-execute, matched in memory | The reference set fits in memory. Default, usually correct |
| Partial Cache | Fetched on demand, cached up to a size limit | Large reference set, pipeline touches a small repeated subset |
| No Cache | One query per incoming row | Reference data changes during the load, or the pipeline is tiny |
Rules that prevent most Lookup problems:
- Restrict the reference query.
SELECT StoreCode, StoreKey FROM dbo.DimStore, never Table or view on a wide table. Full Cache loads exactly what the query returns. - Handle no-match rows deliberately. The setting offers fail, ignore, or redirect. Redirecting is normal design: matched rows → update path, unmatched → insert path.
- Types and collation must match exactly.
DT_STRagainstDT_WSTRwill not match; case- and accent-sensitivity will surprise you. - Full Cache pre-execute cost is real — against a very large dimension the package can appear hung before any row moves.
- Cache Connection Manager lets several lookups, or several packages, share one populated cache instead of reloading it.
Non-equijoin conditions (ranges, LIKE) are not supported. Use Merge Join, or stage and join in SQL.
Merge Join
Section titled “Merge Join”Joins two sorted inputs — inner, left outer, or full outer. Both inputs must be sorted on the join columns, and the engine must be told so.
The correct way to satisfy that without a blocking Sort:
- Add
ORDER BY JoinKeyto both source queries. - On each source component, open the Advanced Editor.
- Set the output’s
IsSortedproperty toTrue. - Set
SortKeyPositionon the join columns —1,2, … for compound keys.
This is an assertion, not a check. If the data is not actually sorted, results are silently wrong. Only claim it when the query really orders the rows.
Choosing between Lookup and Merge Join:
| Situation | Use |
|---|---|
| Both sides in the same database | Neither — join in SQL |
| Small reference set, equality join | Lookup, Full Cache |
| Large sorted sets, or a full outer join | Merge Join |
| Reference set too large to cache, but source is sorted | Merge Join |
Combines two sorted inputs into one sorted output, preserving order. It is not a join — it interleaves. Union All does the same job without the sort requirement, and is what you want unless order must be preserved.
Union All
Section titled “Union All”Concatenates multiple inputs. No sort requirement, no ordering guarantee, cheap. Column mapping is by name and position; mismatched types must be reconciled first.
Typical use: bring the branches of a Conditional Split back together after handling each differently.
Multicast
Section titled “Multicast”Sends every row to multiple outputs without copying — it hands the same buffer to each branch. Multicast itself is nearly free; the branches are not, because they run concurrently and compete for memory and CPU.
Uses: write the same rows to two destinations; send rows to a destination and to a Row Count for auditing.
Aggregate
Section titled “Aggregate”GROUP BY-style results: SUM, COUNT, AVG, MIN, MAX, COUNT DISTINCT. Fully blocking.
Nearly always better in SQL:
SELECT Region, SUM(OrderTotal) AS RegionTotal, COUNT(*) AS OrderCountFROM stg.SalesLineGROUP BY Region;The Aggregate transformation earns its place only when the source cannot aggregate — flat files, or heterogeneous inputs already combined in the pipeline.
Sorts the pipeline. Fully blocking, memory-hungry, and the most over-used component in SSIS. Its “remove duplicate rows” option is often the real reason people reach for it.
Alternatives in order of preference:
ORDER BYin the source query withIsSorted/SortKeyPositionset.- Sorting or deduplicating in the destination database after a staging load.
- Redesigning so nothing needs sorted input (Lookup instead of Merge Join).
Other components worth knowing
Section titled “Other components worth knowing”| Component | Use |
|---|---|
| Row Count | Writes the row count to a variable — the standard way to log what a branch handled |
| Audit | Adds package metadata columns (package name, execution GUID, machine) to each row |
| Script Component | Source, transformation, or destination in C#/VB — the escape hatch for row logic |
| Pivot / Unpivot | Reshapes columns to rows and back; T-SQL PIVOT is usually clearer |
| Slowly Changing Dimension | Wizard-generated SCD handling. Convenient for small dimensions; generates row-by-row OLE DB Commands and does not scale — hand-built staging plus set-based MERGE is the standard alternative |
| Balanced Data Distributor | Splits one input across several outputs to parallelise CPU-heavy downstream work |
| Character Map | Case and character-set operations in place |
| Cache Transform | Populates a Cache Connection Manager for reuse by multiple Lookups |
A realistic end-to-end example
Section titled “A realistic end-to-end example”A fictional nightly sales-line load, from a per-region CSV into a warehouse staging table, splitting new from changed and quarantining the rest.
Flat File Source "SalesFeed" • columns declared: StoreCode DT_STR(10), ProductCode DT_STR(20), SaleDate DT_DBDATE, Quantity DT_I4, UnitPrice DT_DECIMAL(2), DiscountPercent DT_DECIMAL(2) • FastParse enabled on Quantity and SaleDate • error output: Redirect Row │ └──> [error path, see below] ▼Derived Column "Clean and stamp" Region ← UPPER(TRIM([Region])) DiscountRate ← (DT_DECIMAL,4)[DiscountPercent] / 100 LoadedOn ← (DT_DBTIMESTAMP)@[System::StartTime] BatchId ← (DT_I4)@[User::BatchId] SourceFile ← (DT_WSTR,260)@[User::CurrentFilePath] ▼Conditional Split "Validate" ├── "Invalid" : ISNULL([StoreCode]) || ISNULL([SaleDate]) || [Quantity] <= 0 │ └──> Derived Column (reason = "FailedValidation") │ └──> OLE DB Destination → stg.SalesLineReject └── default ▼Lookup "Resolve store" (Full Cache) Reference: SELECT StoreCode, StoreKey FROM dbo.DimStore WHERE IsCurrent = 1 No-match: Redirect rows to no-match output ├── no-match ──> Derived Column (reason = "UnknownStore") ──> stg.SalesLineReject └── match ▼Lookup "Resolve product" (Full Cache) Reference: SELECT ProductCode, ProductKey FROM dbo.DimProduct WHERE IsCurrent = 1 ├── no-match ──> Derived Column (reason = "UnknownProduct") ──> stg.SalesLineReject └── match ▼Multicast ├──> Row Count → User::RowsStaged (audit) └──> OLE DB Destination (fast load) → stg.SalesLine Table lock: ON (dedicated staging table) Check constraints: ON Maximum insert commit size: 50000
Error path from the source:Flat File Source error output ▼Script Component "Describe error" ErrorDescription ← ComponentMetaData.GetErrorDescription(Row.ErrorCode) ErrorColumnName ← GetIdentificationStringByID(Row.ErrorColumn) ▼Derived Column (add package, execution GUID, file name, timestamp) ▼OLE DB Destination → stg.SalesLineRejectThen the control flow — not the pipeline — applies staging to the warehouse with one set-based statement, and compares User::RowsStaged against the reject count to decide whether the run is acceptable.
Why this shape:
- Every rejection path is explicit and labelled with a reason. A reject table you can
GROUP BY reasonis an operations asset; an unlabelled reject file is not. - Two Lookups, both restricted and both redirecting — unknown dimension members are data quality events, not crashes.
- No Sort, no Aggregate, no OLE DB Command. Nothing in this pipeline blocks.
- The destination is staging, and the warehouse apply is SQL.
Pipeline internals
Section titled “Pipeline internals”Execution trees
Section titled “Execution trees”The engine divides the data flow into execution trees (also called buffer subtrees). A new tree begins at a source and at every asynchronous component’s output, because that is where new buffers are allocated.
Source ─> Derived Column ─> Conditional Split ─> Lookup ─> Destination└──────────────── execution tree 1: ONE set of buffers ─────────────┘
Source ─> Derived Column ─> Sort ─> Destination└──── tree 1 ────────────┘ └──── tree 2 (new buffers) ────┘Why it matters:
- Each tree gets its own buffer allocation, so more asynchronous components means more memory.
- The engine assigns threads per tree;
EngineThreadsinfluences how many are used. - With
PerformanceorVerboselogging, the catalog records per-component phase timings — the direct way to see which tree is the bottleneck.
Buffer sizing
Section titled “Buffer sizing”Two Data Flow Task properties govern buffers:
| Property | Meaning | Default |
|---|---|---|
DefaultBufferMaxRows |
Maximum rows per buffer | 10,000 |
DefaultBufferSize |
Target buffer size in bytes | 10 MB |
The engine estimates row width from declared column types and allocates whichever limit binds first. Wide rows hit the byte limit long before the row limit — which is exactly why narrowing rows helps more than raising the limits.
Modern SSIS also exposes AutoAdjustBufferSize: when True, the engine sizes buffers from row width and DefaultBufferMaxRows, removing most of the guesswork.
Buffer reuse and memory pressure
Section titled “Buffer reuse and memory pressure”Within an execution tree, buffers are recycled: a buffer handed downstream and fully consumed returns to the pool. That is why a well-shaped pipeline’s memory use is flat regardless of row count.
Blocking components break this. Sort must hold every row before emitting any, so buffer demand grows with the data. When SSIS cannot allocate more, it spools buffers to disk at BufferTempStoragePath, and throughput collapses. Sustained spooling means either too much memory demand (usually a blocking transformation) or a machine short of RAM — frequently because SSIS is sharing it with the Database Engine.
Metadata and lineage IDs
Section titled “Metadata and lineage IDs”Every column on every path has a lineage ID, assigned at design time and stored in the .dtsx. The engine uses them to lay out buffer columns before reading any data.
Consequences:
VS_NEEDSNEWMETADATAappears when an external object’s shape no longer matches the cached metadata. Fixing it means re-opening the affected components — and it cascades downstream.- Error outputs report
ErrorColumnas a lineage ID, not a name, which is why raw reject data is unreadable until translated (see below). SELECT *in a source guarantees metadata churn every time the underlying table changes.- Data flows cannot change shape at runtime. Metadata-driven loads must keep one shape per data flow.
LOB columns
Section titled “LOB columns”VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML are handled outside the normal buffer layout and spill to disk. Exclude them unless genuinely needed; when needed, isolate them in their own data flow so they do not degrade the main pipeline.
Error outputs
Section titled “Error outputs”Every component that can fail exposes an error output, configured per column for Error (conversion/expression failure) and Truncation (value too long).
| Disposition | Behaviour | Use when |
|---|---|---|
| Fail Component | Component errors, data flow stops | Default. Correct when any bad row invalidates the batch |
| Ignore Failure | Value becomes NULL, row continues | Rarely correct — silently corrupts data |
| Redirect Row | Row goes to the error output | The right answer for most file-based loads |
The error output adds ErrorCode and ErrorColumn. Neither is human-readable, which is why so many reject files are useless. Translate them:
// Script Component (Transformation) on the error path.// Inputs: ErrorCode, ErrorColumn.// Outputs: ErrorDescription, ErrorColumnName (both DT_WSTR)public override void Input0_ProcessInputRow(Input0Buffer Row){ Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
IDTSComponentMetaData130 meta = this.ComponentMetaData as IDTSComponentMetaData130; Row.ErrorColumnName = meta != null ? meta.GetIdentificationStringByID(Row.ErrorColumn) : Row.ErrorColumn.ToString();}Full treatment — thresholds, reject tables, and how this interacts with the control flow — is in Error Handling, Logging, Event Handlers, Transactions, and Checkpoints.
Data viewers and design-time debugging
Section titled “Data viewers and design-time debugging”A data viewer attaches to a path and shows rows passing through it during a debug run in Visual Studio.
- Right-click a path → Enable Data Viewer.
- Execution pauses at each buffer until you click through, so it is unusable on large volumes — filter the source to a handful of rows first.
- Design time only. Data viewers do nothing in a deployed package; leaving one enabled has no production effect but is noise in the diff.
For production diagnosis, the equivalents are:
- Row Count transformations on each branch, written to variables and logged.
Verboselogging, which records rows sent between components incatalog.execution_data_statistics.- A reject table with reasons, which tells you why rows left the happy path.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”Script Component as a source. Useful for reading formats no built-in source handles — irregular files, APIs, hierarchical data. Output columns are declared at design time, so metadata is still fixed.
Script Component as an asynchronous transformation. Setting the output’s SynchronousInputID to None makes it asynchronous, letting it emit a different number of rows than it consumes (splitting one row into many, for example). It then allocates new buffers, with the memory implications that follow.
Balanced Data Distributor parallelises CPU-bound downstream work by fanning rows across several identical branches. It helps when the bottleneck is transformation CPU, not I/O — and it multiplies memory use.
Sorted output claims. IsSorted/SortKeyPosition on any component output is an assertion the engine trusts. Merge and Merge Join rely on it. Wrong claims produce silently wrong results, which is a far worse failure mode than an error.
Concurrent destinations. Two data flows writing to the same table with TABLOCK will block each other and can deadlock. Either serialise, or give each a separate staging table and apply once.
Transactions and the pipeline. A data flow enrolled in a transaction holds locks for its whole duration, and its commit size interacts with that transaction. Long pipelines inside transactions are a reliable source of blocking.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: the “do everything in the pipeline” flow
Section titled “Anti-pattern: the “do everything in the pipeline” flow”BAD: OLE DB Source (SELECT * FROM Orders) ─> Sort (by CustomerId) ─> Merge Join with (SELECT * FROM Customer) also Sorted ─> Aggregate (SUM by Region) ─> OLE DB Command (UPDATE per row)
Two blocking components, two full table reads, one round trip per row.BETTER: OLE DB Source: one query that joins and aggregates in SQL, selecting only the needed columns ─> OLE DB Destination (fast load) → staging Control flow: one set-based apply from stagingIf both sides live in the same database, moving rows into SSIS to join them is pure overhead.
Other frequent mistakes
Section titled “Other frequent mistakes”SELECT *in sources — wide buffers and metadata churn.- Sort or Aggregate mid-pipeline when the source query could do it.
- Lookup on “Table or view” with Full Cache against a huge dimension.
- Ignoring the Conditional Split default output, so unmatched rows vanish.
- Leaving error outputs at Fail Component for file loads, so one bad row kills a million-row job.
- Converting types in the pipeline when the source query or connection manager could produce the right type.
- Adding columns rather than replacing them in Derived Column, widening every row.
- Data viewers left enabled in checked-in packages.
Failure modes, recovery, and troubleshooting
Section titled “Failure modes, recovery, and troubleshooting”| Symptom | Likely cause | Action |
|---|---|---|
| Package hangs before rows move | Full Cache Lookup loading a huge reference set | Restrict the reference query; consider Partial/No Cache |
| Memory climbs until the package fails or crawls | Blocking transformation, or LOB columns | Remove Sort/Aggregate; check BufferTempStoragePath for spool files |
| “Cannot convert between unicode and non-unicode string data types” | DT_STR vs DT_WSTR mismatch |
Fix at the source or connection manager, not with a Data Conversion |
| Truncation errors on a flat file | Default DT_STR(50) widths |
Declare real widths |
| Rows silently disappear | Unconnected Conditional Split default output, or Lookup set to Ignore Failure | Connect the default output; set no-match to Redirect |
| Merge Join returns too few rows | Inputs not actually sorted as claimed, or type/collation mismatch | Verify ORDER BY matches SortKeyPosition; check collations |
| Destination is slow, source is fast | Fast load not enabled, or destination indexes/triggers | Enable fast load; consider loading a heap and indexing after |
| Deadlocks between packages | Concurrent TABLOCK writes to one table |
Serialise or separate staging tables |
VS_NEEDSNEWMETADATA after a source change |
Cached metadata stale | Re-open affected components; stop using SELECT * |
Bottleneck isolation, in order:
- Replace the destination with a Row Count and re-run. Fast now? The bottleneck is the write path.
- Run the source query alone in SSMS, discarding results. Slow there? It is a database problem, not an SSIS one.
- Remove transformations one at a time between source and Row Count to find the expensive one.
- Check
catalog.execution_component_phasesfor per-component phase times. - Watch the machine — sustained CPU points at transformations, disk wait at source/destination, growing memory at a blocking component.
-- Where did a slow data flow spend its time?SELECT subcomponent_name, phase, SUM(DATEDIFF(MILLISECOND, start_time, end_time)) AS elapsed_msFROM catalog.execution_component_phasesWHERE execution_id = @execution_idGROUP BY subcomponent_name, phaseORDER BY elapsed_ms DESC;Performance and resource considerations
Section titled “Performance and resource considerations”Summarised — the full treatment is Large-Volume ETL and Performance Engineering:
- Narrow the rows first. Declared width drives buffer capacity; nothing else you tune matters as much.
- Eliminate blocking components before touching buffer settings.
- Use fast load with a chosen commit size.
- Restrict Lookup reference queries.
- Tune buffers last, and only with measurements.
- Remember SSIS and SQL Server compete for memory on a shared machine.
Security and configuration considerations
Section titled “Security and configuration considerations”- Reject tables and files can contain the sensitive data that failed. They inherit no protection from the package. Secure them, and consider masking before writing.
- Data viewers display real data on a developer’s screen. Avoid pointing debug runs at production data.
- Source queries with embedded credentials (linked servers,
OPENROWSET) leak into the package XML. Parameterise connections instead. Verboselogging records row counts and variable values, which can be sensitive. Use it for troubleshooting, not routinely.
Testing, validation, and operational monitoring
Section titled “Testing, validation, and operational monitoring”- Seed a known input file with a deliberate mix of good rows, type failures, truncations, and unknown dimension members. A pipeline whose error handling has never been exercised does not have error handling.
- Assert after the run, in SQL: rows staged + rows rejected = rows in the file; no orphan keys; control totals match.
- Log Row Counts per branch into the batch audit table, so “how many rows went where” is answerable without re-running.
- Trend the reject rate. Three rejects in a hundred rows is a broken feed; three in ten million is noise. Alert on the ratio, not the count.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| Decision | Option A | Option B | Choose A when |
|---|---|---|---|
| Join | Lookup (Full Cache) | Merge Join | Reference set fits in memory, equality join |
| Join | Either of the above | Join in SQL | — choose SQL whenever both sides share a database |
| Update path | OLE DB Command | Staging + set-based apply | Row count is genuinely tiny |
| Aggregation | Aggregate component | GROUP BY in SQL |
The source cannot aggregate (flat files) |
| Sorted input | Sort component | ORDER BY + IsSorted |
Never — prefer the source ordering |
| Dimension handling | SCD wizard | Hand-built staging + MERGE | Dimension is small and rarely changes |
| Custom row logic | Script Component | A stored procedure after staging | The logic is genuinely row-by-row and not expressible in SQL |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”Q: Explain synchronous vs asynchronous components and why the distinction matters.
Define it by buffer lineage, not row counts. A synchronous output reuses the input’s buffer — each row is processed independently and passed straight on, and in the object model the output’s SynchronousInputID is set to the input’s ID. An asynchronous output gets a new buffer (SynchronousInputID = 0) because the component must accumulate rows, combine multiple inputs, or emit a different set of rows. Do not say “rows in equals rows out”: Conditional Split is synchronous and routes each row to exactly one of several outputs via ExclusionGroup/DirectRow, and error outputs redirect rows the same way with DirectErrorRow — all still sharing one buffer. Why it matters: asynchronous components multiply memory use, and the fully blocking subset (Sort, Aggregate) must consume the entire input before emitting anything, turning a streaming pipeline into a batch one that must fit in memory or spill to disk.
Q: A data flow’s memory grows steadily until the package slows to a crawl. Diagnose it.
Look for a fully blocking transformation — Sort, Aggregate, Fuzzy Lookup — or a Full Cache Lookup against an oversized reference set. Confirm by checking for spool files at BufferTempStoragePath and by inspecting catalog.execution_component_phases. The fix is structural: move the sort or aggregation into the source query, restrict the Lookup reference query, or stage and do the work in SQL.
Q: Why is ErrorColumn not enough to diagnose a rejected row?
It is a lineage ID — an internal design-time identifier for a column on a path — not a column name. To make rejects actionable you translate it with GetIdentificationStringByID and translate ErrorCode with GetErrorDescription, in a Script Component on the error path, then add operational context (package, execution GUID, source file, timestamp).
Q: You need to sort data for a Merge Join. What do you do, and what is the risk?
Add ORDER BY to both source queries, then set IsSorted = True and SortKeyPosition on the join columns in each source’s Advanced Editor. That avoids a blocking Sort. The risk is that this is an assertion the engine trusts without checking — if the query does not actually return rows in that order, Merge Join produces silently wrong results rather than an error.
Q: When is Ignore Failure on an error output ever the right choice?
Almost never. It replaces the failing value with NULL and lets the row continue, which silently changes data without any record that it happened. The rare defensible case is an optional enrichment column where NULL is a legitimate, expected value and the row remains valid without it — and even then, counting how often it occurs is wiser than ignoring it.
Q: A load into a large fact table is slow. Walk through your investigation. Isolate first: swap the destination for a Row Count. If it becomes fast, the write path is the problem — check fast load is enabled, look at commit size, count the nonclustered indexes and triggers on the target, and check for lock contention with concurrent loads. If it is still slow, isolate the source by running its query alone in SSMS; if that is slow, it is a query/index problem. Only if both ends are fast do you look at the transformations in between, and only after that at buffer settings.
Q: What breaks if two developers both add a column to the same data flow?
The .dtsx XML merge. Lineage IDs and layout are regenerated, so the diff is large and the merge is effectively unresolvable by hand. The practical answer is process, not tooling: treat packages as exclusively locked, keep them small, and when a conflict happens take one side wholesale and re-apply the other change in the designer.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Fundamentals
- SSIS Control Flow: Tasks, Containers, and Precedence Constraints
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
- SSIS Large-Volume ETL and Performance Engineering
- SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
- SQL Server Indexes
- SQL Server Data Types
- SQL Server Joins
Official Microsoft references
Section titled “Official Microsoft references”- Data Flow
- Understanding Synchronous and Asynchronous Transformations
- Integration Services Transformations
- Conditional Split Transformation
- OLE DB Destination
- OLE DB Custom Properties
- Lookup Transformation
- Merge Join Transformation
- Sort Transformation
- OLE DB Command Transformation
- Error Handling in Data
- Sort Data for the Merge and Merge Join Transformations
- Data Flow Performance Features