| name | ssis-package-patterns |
| description | Use when generating, reviewing, or extending the four supported SSIS package patterns: staging load, Type-1 dimension, Type-2 (SCD-2) dimension, and fact load. Describes the control-flow / data-flow shape, the managed object model call sequence, and the metadata-JSON fields each pattern consumes. |
Supported SSIS package patterns
The toolkit emits exactly four pattern shapes. Anything else is refused at the agent boundary. Each pattern lives in a PowerShell module under tools/lib/patterns/ and consumes a metadata JSON of a specific shape (full schema: templates/metadata/schema.json).
When to load this skill
- Generating a new package — pick the pattern, read the matching section below.
- Reviewing a diff that touches a pattern module.
- Deciding whether a user request fits a pattern or needs a refusal.
Common preamble (every pattern)
Every generated package:
ProtectionLevel = DontSaveSensitive on package and project.- Connection managers
Source and Warehouse come from templates/ssis-project/ConnectionManagers/*.conmgr (created once by scaffold-new-ssis-project).
- Project parameters
RunDate (datetime, default GETDATE()), BatchId (string, default NEWID()), LoadedByPackageRunId (int) are bound automatically by the OM helper.
- Every destination component writes the audit columns
LoadedAt and LoadedByPackageRunId.
Pattern 1: Staging Load (pattern: "staging")
Intent: copy a source table into stg.<Name>. Truncate first by default.
Control flow:
Execute SQL Task — TRUNCATE TABLE stg.<Name> (skipped if truncateBeforeLoad: false).Data Flow Task:
OLE DB Source — sourceQuery.Derived Column — adds LoadedAt = @[$Project::RunDate], LoadedByPackageRunId = @[$Project::LoadedByPackageRunId].OLE DB Destination — targetTable, fast-load, table lock, rows per batch 10000.
Execute SQL Task — INSERT INTO etl.PackageRun (PackageName, RowsLoaded, …) VALUES (…).
OM module: tools/lib/patterns/StagingLoad.psm1 → New-StagingPackage -Metadata $obj -OutputPath <…>.dtsx.
Pattern 2: Type-1 Dimension (pattern: "type1-dim")
Intent: SCD-1 — overwrite payload on key match; insert new row otherwise.
Control flow:
Data Flow Task:
OLE DB Source — SELECT <BK>, <payload…> FROM stg.<Name>.Lookup against dim.<Name> on businessKey. No match → insert path; match → update path.- Insert path:
OLE DB Destination into dim.<Name> (SK auto-generated by IDENTITY).
- Update path:
OLE DB Command running UPDATE dim.<Name> SET <payload> = ?, LoadedAt = ? WHERE <surrogateKey> = ?.
OM module: tools/lib/patterns/Type1Dimension.psm1 → New-Type1DimensionPackage -Metadata $obj -OutputPath ….
Pattern 3: Type-2 Dimension (pattern: "type2-dim")
Intent: SCD-2 — payload change creates a new row, marks prior IsCurrent = 0, sets EffectiveTo.
Control flow:
Data Flow Task:
OLE DB Source — SELECT <BK>, <payload…> FROM stg.<Name>.Lookup against dim.<Name> on businessKey filtered by IsCurrent = 1. Returns current SK + current payload.Conditional Split:
- No match → insert as new.
- Match + payload unchanged → discard (no-op output).
- Match + payload changed → two outputs: expire-old + insert-new.
- Expire-old:
OLE DB Command running UPDATE dim.<Name> SET IsCurrent = 0, EffectiveTo = @RunDate WHERE <surrogateKey> = ?.
- Insert-new AND insert-as-new:
OLE DB Destination with IsCurrent = 1, EffectiveFrom = @RunDate, EffectiveTo = NULL.
OM module: tools/lib/patterns/Type2Dimension.psm1 → New-Type2DimensionPackage -Metadata $obj -OutputPath ….
Required dim columns: IsCurrent bit NOT NULL, EffectiveFrom datetime2(3) NOT NULL, EffectiveTo datetime2(3) NULL. The generator refuses to emit if any are missing.
Pattern 4: Fact Load (pattern: "fact")
Intent: resolve dim surrogate keys via Lookup, then insert into fact.<Name>.
Control flow:
Data Flow Task:
OLE DB Source — SELECT * FROM stg.<Name>.- One
Lookup per entry in dimensionLookups[]. Each lookup matches on joinOn, returns factColumn (the SK). Error output of every Lookup routes to a Row Count → audit row in etl.RowAudit so unmatched rows are captured.
OLE DB Destination into fact.<Name> with all measures + resolved SKs.
OM module: tools/lib/patterns/FactLoad.psm1 → New-FactLoadPackage -Metadata $obj -OutputPath ….
Refuse: if any dim in dimensionLookups is empty (SELECT COUNT(*) = 0). An empty dim means every Lookup misses, which silently null-routes rows.
How OM helpers compose
All four patterns call into shared helpers in tools/lib/SsisOm.psm1:
New-SsisPackage -Name 'Stg_Customer' -ProtectionLevel DontSaveSensitive
Add-OleDbConnection -Package $pkg -Name 'Source' -ServerName … -InitialCatalog …
Add-DataFlowTask -Package $pkg -Name 'DFT Load stg.Customer'
Add-OleDbSource -DataFlow $dft -Name 'Src' -Connection 'Source' -SqlCommand …
Add-OleDbDestination -DataFlow $dft -Name 'Dst' -Connection 'Warehouse' -Table 'stg.Customer'
Connect-DataFlowComponents -From $src -To $dst
Save-SsisPackage -Package $pkg -Path …
The OM threads all refIds and lineageIds automatically.
References
