Ssis-927 !!top!! -

| Technique | Rationale | Measured Impact | |---|---|---| | | Adjusted DefaultBufferMaxRows (10 000 → 30 000) and DefaultBufferSize (10 MB → 100 MB) to match Azure VM memory profiles. | 22 % reduction in overall runtime. | | Parallel Execution | Enabled EngineThreads = 8 and configured MaxConcurrentExecutables to 4 per package. | Achieved near‑linear speed‑up across 4 SSIS nodes. | | Data Flow Partitioning | Added Partitioned Lookup on large dimension tables (e.g., Product, Store). | Lookup latency dropped from 2.8 s to 0.4 s per 1 M rows. | | Avoiding Row‑by‑Row Operations | Replaced iterative OLE DB Command components with set‑based MERGE statements. | Cut incremental load time from 90 min → 38 min for the largest fact table. |

Test Plan

| Transformation | When to Use | Tips | |----------------|------------|------| | | Small reference tables (≤ 2 M rows). | Set Cache mode = Full for fastest performance. | | Lookup (Partial/No Cache) | Large tables, memory‑constrained. | Use Partial and set CacheSize appropriately. | | Merge Join | Joining two sorted streams. | Sort upstream to avoid spool; use Inner Join for performance. | | Script Component (Transformation) | Complex row‑level logic (e.g., regex, custom hashing). | Write in C#; expose ReadOnly and ReadWrite columns via Inputs and Outputs . | | Conditional Split | Route rows based on expression. | Combine multiple predicates in one split to reduce downstream components. | | Data Conversion | Convert data types before loading to destination. | Prefer native source conversions where possible (e.g., set DataType on OLE DB Source). | | Multicast | Duplicate a data stream to several branches. | Use sparingly; each branch adds a buffer copy. | | Recordset Destination | Store rows in an ADO.NET Recordset for later use in a Script Task. | Not recommended for large rowsets (use staging tables instead). | SSIS-927