adventureworks-mapping

name	adventureworks-mapping
description	Use when authoring SSIS packages, metadata JSON, or DDL that reads from AdventureWorks2025. Pins the canonical source-table → demo-staging mapping so the agent never invents column names. Required reading before any /generate-*-package prompt that targets the demo schema.

AdventureWorks2025 → demo schema mapping

The toolkit's demo uses AdventureWorks2025 (attached on .\SQL2025) as the source system. Demo loads land in:

• CopilotSSIS_Warehouse.stg.* — staging
• CopilotSSIS_Warehouse.dim.* — dimensions (mix of Type-1 and Type-2)
• CopilotSSIS_Warehouse.fact.* — facts
• CopilotSSIS_Warehouse.etl.* — audit/metadata

This skill is the single source of truth for source-column names and target-column shape. Agents never infer these from training data.

When to load this skill

• Any /generate-*-package prompt that uses AdventureWorks2025 as source.
• Authoring DDL for stg.* or dim.* tables that mirror an AW source.
• Building validation SQL that compares warehouse row counts to source.

Verification first

Before relying on any mapping below, the agent should confirm the source still exists in the target DB:

USE AdventureWorks2025;
SELECT TOP 5 * FROM Sales.Customer;
SELECT TOP 5 * FROM Person.Person WHERE BusinessEntityID IN (SELECT PersonID FROM Sales.Customer);

If a referenced table is missing, refuse and surface the gap to the user — do not invent column names.

Canonical mappings (demo Phase 1–7)

dim.Customer (SCD-2)

Source	AW table	AW column	Target column	Notes
Sales.Customer	CustomerID	CustomerBK	int, business key
Person.Person	FirstName	FirstName	payload, SCD-2
Person.Person	LastName	LastName	payload, SCD-2
Person.EmailAddress	EmailAddress	EmailAddress	payload, SCD-2
Sales.Customer	StoreID	StoreID	payload, SCD-2
Sales.Customer	TerritoryID	TerritoryID	payload, SCD-2
Sales.Customer	AccountNumber	AccountNumber	payload, SCD-2
generated	—	CustomerSK	identity surrogate key
audit	—	IsCurrent, EffectiveFrom, EffectiveTo, LoadedAt, LoadedByPackageRunId	SCD-2 + audit

Source SELECT (for the staging package):

SELECT  c.CustomerID,
        p.FirstName,
        p.LastName,
        e.EmailAddress,
        c.StoreID,
        c.TerritoryID,
        c.AccountNumber
FROM    Sales.Customer c
LEFT JOIN Person.Person p       ON p.BusinessEntityID = c.PersonID
LEFT JOIN Person.EmailAddress e ON e.BusinessEntityID = c.PersonID;

dim.Product (Type-1)

Source	AW table	AW column	Target column
Production.Product	ProductID	ProductBK
Production.Product	Name	ProductName
Production.Product	ProductNumber	ProductNumber
Production.Product	Color	Color
Production.Product	ListPrice	ListPrice
Production.ProductSubcategory	Name	SubcategoryName (joined via ProductSubcategoryID)
Production.ProductCategory	Name	CategoryName (joined via subcategory → category)
generated	—	ProductSK

Source SELECT:

SELECT  p.ProductID,
        p.Name           AS ProductName,
        p.ProductNumber,
        p.Color,
        p.ListPrice,
        ps.Name          AS SubcategoryName,
        pc.Name          AS CategoryName
FROM    Production.Product p
LEFT JOIN Production.ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
LEFT JOIN Production.ProductCategory    pc ON pc.ProductCategoryID    = ps.ProductCategoryID;

dim.Date (static, generated by SQL not SSIS)

Generated by templates/sql/03-warehouse-schema.sql — a date table covering 2010-01-01 to 2030-12-31. Columns: DateSK (int PK, YYYYMMDD form), DateValue (date), Year, Quarter, Month, MonthName, Day, DayName, IsWeekend, FiscalYear, FiscalQuarter. No SSIS package loads this — referenced only as a Lookup target by the fact load.

fact.SalesOrder

Source	AW column	Target column
Sales.SalesOrderDetail	SalesOrderID	SalesOrderBK (degenerate dimension)
Sales.SalesOrderDetail	SalesOrderDetailID	SalesOrderDetailBK (degenerate dimension)
via Sales.SalesOrderHeader	CustomerID → Lookup → dim.Customer.CustomerBK	CustomerSK
Sales.SalesOrderDetail	ProductID → Lookup → dim.Product.ProductBK	ProductSK
via Sales.SalesOrderHeader	OrderDate → format YYYYMMDD → dim.Date.DateSK	DateSK
Sales.SalesOrderDetail	OrderQty	OrderQuantity (measure)
Sales.SalesOrderDetail	UnitPrice	UnitPrice (measure)
Sales.SalesOrderDetail	LineTotal	LineTotal (measure)

Source SELECT (for the staging package):

SELECT  d.SalesOrderID,
        d.SalesOrderDetailID,
        h.CustomerID,
        d.ProductID,
        CONVERT(int, CONVERT(varchar(8), h.OrderDate, 112)) AS OrderDateSK,
        d.OrderQty,
        d.UnitPrice,
        d.LineTotal
FROM    Sales.SalesOrderDetail d
JOIN    Sales.SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID;

etl.* tables (created by templates/sql/04-audit-tables.sql)

Table	Purpose
etl.PackageRun	One row per package execution. Captures PackageRunId, PackageName, StartedAt, EndedAt, Status, RowsRead, RowsWritten.
etl.RowAudit	Row-level audit for unmatched lookups, error-output rows.
etl.ParameterOverride	Optional per-environment parameter override values (used by SSISDB environment references).

What this skill does NOT cover

• AdventureWorks2025 columns not used by the demo (e.g. HumanResources.Employee, Production.WorkOrder). If a future demo expansion needs them, extend this skill — don't infer from prior AdventureWorks experience.
• Source-schema variants (AdventureWorksLT, AdventureWorksDW). The toolkit targets the OLTP AdventureWorks2025 only.

References

• AdventureWorks downloads / schema: https://learn.microsoft.com/sql/samples/adventureworks-install-configure
• AW database diagram (high-level): https://learn.microsoft.com/sql/samples/adventureworks-install-configure#download-backup-files