Ssis-948 Upd

| Layer | Description | |-------|-------------| | | Responsible for calculating optimal chunk size based on runtime statistics (available memory, target table row‑size, index fragmentation). It continuously adjusts the size (± 20 %) to keep the pipeline at ~80 % CPU utilization. | | Write Workers | A pool of lightweight Task objects (default = number of logical cores) that perform SqlBulkCopy operations in ordered fashion, preserving row order when required. | | Validation Engine | Executes user‑defined expressions (the same syntax used in SSIS Derived Column transformations) on each row before it enters a chunk. Failed rows are sent to the Error Output stream without stopping the pipeline. | | Back‑Pressure Controller | Monitors downstream queue depth. If the write workers cannot keep up, the controller throttles the upstream Source components by applying a pause on the data‑reader. This prevents OOM crashes and excessive transaction log growth. | | Telemetry & Logging | Emits granular performance counters (rows/second per worker, average chunk latency, rollback count) to the SSIS logging framework, Application Insights, or any custom event sink. |

| Scenario | Recommended Destination | |----------|--------------------------| | (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) | ssis-948