| Item | Version | Installation Steps |
|------|---------|--------------------|
| SQL Server | 2019‑2024 (Express, Standard, or Enterprise) | Use the SQL Server Installation Center → Database Engine Services. Enable Integration Services feature. |
| SQL Server Data Tools (SSDT) | Visual Studio 2022 (Community/Professional/Enterprise) | In VS Installer → Individual components → “SQL Server Integration Services” and “SQL Server Data Tools”. |
| .NET SDK | .NET 8 (for script tasks) | dotnet sdk install 8.0 (Windows: use the MSI). |
| PowerShell | 7.x (cross‑platform) | winget install Microsoft.PowerShell |
| Azure CLI & AzCopy | Latest | az login; azcopy for large file moves. |
| Optional – Azure Data Factory (ADF) Integration Runtime | – | az synapse workspace create → Managed Integration Runtime for hybrid pipelines. |
Tip: Create a dedicated Windows 11 VM or a Docker container with the above components for repeatable labs. A Dockerfile example is provided in the GitHub repo linked at the end of the course. SSIS-927
| Technique | Rationale | Measured Impact |
|---|---|---|
| Buffer Size Optimization | 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. | | Item | Version | Installation Steps |
| Guideline | Rationale | Example |
|-----------|-----------|---------|
| Modular Packages | One logical responsibility per package; easier to test & reuse. | “Stg_OracleToStaging”, “Dim_Customer_SCD2”, “Fact_Sales_Load”. |
| Consistent Naming | Improves readability, searchability, and governance. | <Layer>_<Source>_<Target>_[Action] |
| Document Inside | Use package description, annotations, and a README file. | Right‑click → Properties → Description = “Loads daily sales from POS”. |
| Source Control | Store .dtsx and .ispac files in Git; use .gitignore for .user files. | git add *.dtsx *.ispac && git commit -m "Initial commit" |
| Versioning | Deploy via SSISDB → version numbers map to Git tags. | Tag: v1.2.0‑stg‑sales. | Tip: Create a dedicated Windows 11 VM or