Ssis-948

Jenna opened the SSIS catalog in SQL Server Management Studio and inspected the execution logs for the last three days. The pattern was clear:

| Date | Rows Processed | Rows Failed (SSIS‑948) | |------------|----------------|------------------------| | 2026‑04‑13 | 1,245,678 | 12 | | 2026‑04‑12 | 1,247,011 | 0 | | 2026‑04‑11 | 1,246,532 | 7 |

Only a handful of rows were being rejected, but each failure stopped the entire package because the FailPackageOnFailure flag was set on the OLE DB Destination.

She pulled the source query from the Data Flow: ssis-948

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    ShipDate,
    TotalAmount
FROM dbo.StagingOrders
WHERE LoadDate = CAST(GETDATE() AS DATE);

The destination table dbo.FactSales defined OrderDate as NOT NULL and had a CHECK constraint that the date must be on or after 1900‑01‑01. Nothing seemed wrong at first glance.

Jenna then ran a quick ad‑hoc query to see if any rows actually had a NULL OrderDate:

SELECT TOP 10 *
FROM dbo.StagingOrders
WHERE LoadDate = CAST(GETDATE() AS DATE)
  AND OrderDate IS NULL;

The result set was empty. Yet the error insisted a NULL was trying to sneak through. Jenna opened the SSIS catalog in SQL Server

If the connection string uses variables or project parameters:

-- Example: In a script task you may see
Dts.Variables["User::MyConnString"].Value = 
    "Data Source=ProdServer;Initial Catalog=Sales;Integrated Security=SSPI;";

| Scenario | Recommended Destination | |----------|--------------------------| | Bulk‑load into a regular OLTP table (clustered index on PK) | SSIS‑948 (smart chunking + parallel bulk‑copy) | | Loading a staging table that will be swapped | Standard OLE DB Destination (no need for validation) | | Appending to a table with heavy triggers | SSIS‑948 with TransactionMode = Savepoint to isolate trigger failures per chunk | | Streaming data into a NoSQL sink (e.g., Azure Cosmos DB) | Custom Script Component (SSIS‑948 only supports relational destinations) |

Overview
SSIS‑948 (often called the Smart‑Chunked Data Pump) is a built‑in, high‑performance data‑movement component introduced with SQL Server Integration Services 2019 CU4. It is designed to replace the classic OLE DB Destination / SQL Server Destination when loading very large fact tables, slowly‑changing‑dimension (SCD) tables, or any scenario where: The destination table dbo

| Requirement | How SSIS‑948 Helps | |------------|---------------------| | Massive row counts (hundreds of millions to billions) | Dynamically breaks the load into optimal “chunks” (default 10 000 rows) that are sized based on target table indexes, memory pressure, and transaction log throughput. | | Minimal impact on source systems | Uses asynchronous read‑ahead and pipeline‑back‑pressure to keep the source connection open only for the time needed to fill the next chunk, dramatically reducing lock time on the source. | | High‑throughput network environments | Leverages Multiple Active Result Sets (MARS) and batch‑insert (INSERT … VALUES (…) , (…) , …) for up to 1 000 rows per round‑trip, automatically falling back to tabular‑direct bulk‑copy when the network latency exceeds a configurable threshold. | | Transactional safety | Each chunk runs inside its own autocommit transaction, with an optional save‑point mode that allows you to roll back only the offending chunk rather than the whole batch. | | Built‑in data‑quality checks | Offers declarative pre‑load validation rules (null‑ability, range checks, foreign‑key existence) that are evaluated in‑flight without a separate data‑flow path. Invalid rows are diverted to a configurable Error Output (flat file, Azure Blob, or a staging table). | | Scalability on modern hardware | Detects the number of logical processors and automatically spawns parallel writer threads (up to MAXDOP‑configured value) that write to the same destination table using partition‑aware bulk‑copy, ensuring minimal latch contention. |

The algorithm is deterministic (same input → same adaptive path) and reproducible across runs, which is essential for debugging and compliance.