Ssis-948

| Requirement | How SSIS‑948 Helps | |------------|---------------------| | (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. |

| ✅ | Recommendation | |----|----------------| | on the destination tables before running the package. SSIS‑948 uses the optimizer’s row‑size estimate to compute chunk size. | | 2. Align MaxParallelism with MAXDOP on the target DB, unless you have a dedicated load‑only replica. | | 3. Use ChunkCommit for most ELT jobs; only switch to AllOrNone for small master‑data loads where atomicity is paramount. | | 4. Enable Bulk‑Copy fall‑back ( NetworkLatencyThresholdMs ) when copying to Azure SQL Database or Amazon RDS, where network jitter can degrade row‑by‑row inserts. | | 5. Capture the ErrorOutput to a staging table first, then run a data‑quality cleanse before a second pass. | | 6. Monitor the built‑in performance counters ( SmartChunkedRows/sec , ChunkLatency , RollbackCount ) during the first few runs to fine‑tune ChunkSize or MaxParallelism . | | 7. Turn off EnableTelemetry only after you have verified that the logging overhead is negligible (< 0.5 % CPU). | ssis-948

She added a step before the Derived Column, filtering out rows where both dates are NULL and the order status is not “Shipped”. The new source query became: | | Transactional safety | Each chunk runs

UX & Configuration

[INFO] 2026-04-16 02:13:05 – Package started. [INFO] 2026-04-16 02:13:07 – Data Flow: 1,245,678 rows read. [INFO] 2026-04-16 02:13:09 – Derived Column applied. [INFO] 2026-04-16 02:13:10 – OLE DB Destination: 1,245,666 rows inserted. [INFO] 2026-04-16 02:13:11 – 12 rows redirected to FactSales_Rejected. [INFO] 2026-04-16 02:13:12 – Package completed successfully. | | Scalability on modern hardware | Detects

To resolve the SSIS-948 error, follow these troubleshooting steps: