The Slowly Changing Dimension transform sits right there in the SSIS Toolbox, one drag, a wizard with a few clear questions, and it builds an entire dimension-update pipeline for you. It looks like exactly the shortcut you want. It is also, almost without exception, the slowest way to load a dimension table, and the reason has nothing to do with the concept of slowly changing dimensions itself, it's what the wizard generates underneath.
What the Wizard Actually Builds
Drop the SCD transform into a data flow and answer its questions (business key, Type 1/Type 2/fixed attributes), and it expands into a small cluster of components: a Conditional Split routing rows into new/changed/historical/fixed paths, an OLE DB Destination for brand-new rows, and, for every path that touches an existing row, an OLE DB Command transform running a parameterized UPDATE statement.
That last part is the problem. An OLE DB Command transform executes its SQL statement once per incoming row, a separate round trip to the database for every single changed dimension member. Type 2 tracking makes it worse: expiring the old row and inserting the new one are two separate per-row operations, not one.
Why It's Slow
| Behavior | SCD Wizard (OLE DB Command) | Set-based alternative |
|---|---|---|
| Database round trips | One per changed row (two for Type 2 changes) | One per batch, regardless of row count |
| Transaction log activity | Many small individual transactions | One larger, more efficient transaction |
| Parallelism | Serialized: the data flow blocks on each command | Set-based engine operations, optimizer-parallelizable |
| Scaling behavior | Runtime grows roughly linearly with row count | Dominated by set size, not row-by-row overhead |
On a dimension with a few hundred rows, none of this matters, the wizard's overhead is invisible. On a customer or product dimension with hundreds of thousands of rows and a meaningful daily change rate, the same package can turn a load that should take seconds into one that takes hours.
The Maintenance Trap
Architecture at a Glance
What to Use Instead
- Stage the source data into a plain staging table using a fast-load OLE DB Destination, this is a single bulk operation regardless of row count.
- Detect changes with a Lookup or a hash comparison. Compute a
HASHBYTESchecksum over the tracked attributes on both source and destination side; a mismatch means the row changed. This replaces column-by-column comparisons and works well inside the data flow. - Split new rows from changed rows with a Conditional Split, same idea as the wizard, just without an OLE DB Command downstream of it.
- Load new rows with a fast-load destination. No per-row overhead, this is exactly what bulk loading is for.
- Apply changes with one set-based statement in an Execute SQL Task after the data flow finishes, an
UPDATE ... FROMjoined to the staging table for Type 1 attributes, or an explicit expire-then-insert pair of set-based statements for Type 2 history, still only two statements total, not two per row.
MERGE statement against the staging table can replace steps 4 and 5 in one go. It's convenient, but T-SQL MERGE has documented correctness and concurrency pitfalls under certain isolation levels. If your dimension load can run concurrently with anything else touching that table, read T-SQL MERGE: The Synchronization Statement That Needs Caution before reaching for it, separate UPDATE and INSERT statements are often the safer choice for exactly the same performance gain.
When the Built-In Component Is Still Fine
- Small dimensions, a few hundred to low thousands of rows, where the per-row overhead never adds up to anything noticeable.
- Infrequent batch loads where a nightly job finishing in three minutes instead of thirty seconds isn't a problem anyone will ever notice.
- Prototypes and one-off migrations where getting something working today matters more than how it scales.
The line to watch for is growth: a dimension that's fine today at five thousand rows with the wizard's output will not be fine at five hundred thousand. If there's any realistic chance of that growth, build the set-based pattern from the start, it isn't meaningfully more code to write.
Auditing What You Already Have
If you've inherited a package estate and don't know how many dimension loads still use the wizard's output, you don't have to open every package by hand. SSIS Analyzer parses each package's data flow directly from the underlying XML, including every component's creation name, so you can inventory which packages still route rows through an OLE DB Command before deciding where a rewrite is actually worth the effort.
The Bottom Line
The SCD wizard isn't wrong about what a dimension load needs to do, detect new rows, detect changed rows, apply Type 1 or Type 2 logic. It's wrong about how it does it: one database round trip per row is the one design choice that doesn't scale, no matter how good everything else about the package is. Stage, compare with a hash or Lookup, and finish with one set-based statement instead of thousands of small ones, the logic is the same, the runtime is not.