Ssis698 Full -
Devin didn’t rewrite it. He reverse-engineered the business rule:
“If the last full load was more than 30 days ago, or if the delta record count exceeds 20% of the base table, run FULL. Otherwise, run incremental.”
He added a Pre-execute SQL task that:
Then he modified the package entry point:
If control_table.Mode = 'FULL' → Execute ssis698_full.dtsx (with row sampling)
Else → Execute ssis698_delta.dtsx
He also split the FULL data flow into batched chunks using an INT modulus on the primary key — saving memory and preventing timeouts. ssis698 full
After deployment, ssis698 full went from 14 hours → 22 minutes on full load. Delta loads: 90 seconds.
Performance‑tuning checklist:
| Tuning Lever | Effect | Recommended Starting Point |
|--------------|--------|----------------------------|
| DefaultBufferMaxRows | Controls rows per buffer. | Keep default (10,000) unless rows are huge (> 1 KB). |
| DefaultBufferSize | Controls buffer size in bytes. | Increase to 8–10 MB for wide rows; stay below 104 857,600 (100 MB). |
| EngineThreads | Parallelism of buffers. | Set to Number of logical CPUs – 1 (or let SSIS auto‑detect). |
| Blocking Transformations (e.g., Sort, Aggregate) | Introduces full buffer flushes. | Use SQL ORDER BY or HASH aggregations when possible; if required, enable “RetainSameConnection” to avoid re‑opening connections. |
| Data Access Mode (OLE DB Source) | FastLoad vs. TableOrView vs. Command. | Use FastLoad for bulk inserts; set Rows per batch & Maximum insert commit size appropriately (e.g., 10,000 rows). |
| Component | Purpose | Key Settings |
|-----------|---------|--------------|
| File System Watcher (Script Task) | Detect new files, write their names to an SSIS variable. | FileSystemWatcher class, Created event, populate User::FileList. |
| ForEach Loop (File Enumerator) | Iterate over each CSV file. | FileSpec = *.csv; variable mapping → User::CurrentFile. |
| Data Flow – Staging Load | Load raw rows into stg_Sales (no transformation). | Flat File Source → OLE DB Destination (FastLoad). |
| Execute SQL Task – CDC | Capture rows inserted in the last 24 h (GETDATE() - 1). | INSERT INTO dbo.FactSales SELECT … FROM stg_Sales WHERE LoadDate > @LastRun. |
| Script Component – SCD Type‑2 | Detect changes to DimProduct and insert new version rows. | Use Lookup to find existing product; if change → Insert new row with EffectiveFrom/EffectiveTo. |
| Error Output | Redirect rows that fail conversion to err_Sales. | Redirect Row → Flat File Destination for audit. |
| Logging | Write execution details to dbo.SSIS_ExecutionLog. | SSISDB built‑in logging + custom Execute SQL in OnPostExecute. |
| Cleanup | Move processed files to archive/ folder. | File System Task (Move). | Devin didn’t rewrite it
