Ssis-834 May 2026

| Fix # | Description | Implementation Details | |-------|-------------|------------------------| | 1 | Limit FastLoad commit size | Set FastLoadMaxInsertCommitSize = 10 000 rows. This forces SSIS to commit every 10 k rows, dramatically reducing the transaction size. | | 2 | Tempdb health‑check task | Added a Execute SQL Task at the start of the package that runs:
SELECT total_log_size_in_bytes/1024/1024 AS LogSizeMB, total_size_in_bytes/1024/1024 AS DataSizeMB FROM sys.dm_db_file_space_usage;
and fails the package if DataSizeMB > 85 % of total tempdb size. | | 3 | Tempdb configuration | DBAs increased tempdb to six 4 GB files and set autogrowth = 512 MB (fixed). | | 4 | Error handling | Wrapped the Data Flow Task in a Retry Loop (max 3 attempts) with a delay of 2 min to handle transient deadlocks. | | 5 | Documentation | Updated the package documentation and the SSIS Best‑Practices wiki with a section on FastLoad commit sizing. |

| Test | Description | Pass/Fail | Remarks | |------|-------------|-----------|---------| | Unit Test – Commit Size | Executed package on dev server with FastLoadMaxInsertCommitSize = 10 000. Verified that tempdb usage stayed < 30 % and no OLE DB errors. | Pass | – | | Load Test – 10 M rows | Simulated a worst‑case load (10 M rows, ~ 13 GB) on a replica server. Package completed in 2 h 45 min (vs. > 6 h before). | Pass | Improved ETL window. | | Concurrent Run | Ran the fixed package simultaneously with the large “Dim‑Customer” load. Tempdb usage peaked at 62 % with no deadlocks. | Pass | – | | Rollback Test | Intentionally caused a failure after 3 commits. Verified that only the committed batches persisted and the rollback was clean. | Pass | – | | Production Smoke Test | Deployed to production on 2026‑04‑04. Monitored first 3 nightly runs. No errors observed; tempdb remained < 55 %. | Pass | – | SSIS-834

Investigation revealed a combination of issues: | Fix # | Description | Implementation Details