Creating a simple Master Child pattern within Sql Server 2012 Integration Services
The Master package for loading child packages in Integration Services 2012
The child extract package pattern in Integration Services 2012
The child dimension load pattern, extending the Slowly Changing Dimension
A pattern for loading fact tables with inserted, updated and deleted data
Project Deployment with Sql Server 2012 Integration Services
This pattern will demonstrate loading deltas into a fact table by comparing what's already in the warehouse with what's in staging. Again, all the transformation logic is within the staging view, making development faster and easier to debug. More information for this design decision is here, along with the control flow description, which is identical for this package.
The transformation and load logic is embedded within the view. The key aspects of the view definition are
create view dbo.vw_Fact_Orders as select OrderId, ProductKey, EmployeeKey, OrderDateKey, RequiredDateKey, ShippedDateKey, UnitPrice, Quantity, Discount, HashCode = cast(hashbytes('MD5', coalesce(cast(OrderId as nvarchar), '') + coalesce(cast(ProductKey as nvarchar), '') + coalesce(cast(EmployeeKey as nvarchar), '') + coalesce(cast(OrderDateKey as nvarchar), '') + coalesce(cast(RequiredDateKey as nvarchar), '') + coalesce(cast(ShippedDateKey as nvarchar), '') + coalesce(cast(UnitPrice as nvarchar), '') + coalesce(cast(Quantity as nvarchar), '') + coalesce(cast(Discount as nvarchar), '') ) as varbinary(16)) from ( select o.OrderId, ProductKey = p.ProductKey, --CustomerKey = , EmployeeKey = e.EmployeeKey, OrderDateKey = do.DateKey, RequiredDateKey = dr.DateKey, ShippedDateKey = ds.DateKey, UnitPrice = od.UnitPrice, Quantity = od.Quantity, Discount = od.Discount from dbo.stage_Orders o join dbo.Stage_OrderDetails od on o.OrderId = od.OrderId join DW_Northwind.dbo.vw_Dim_Employee e on o.EmployeeID = e.EmployeeId join DW_Northwind.dbo.Dim_Product p on od.ProductID = p.ProductId join DW_Northwind.dbo.Dim_Date do on cast(convert(varchar(8), o.OrderDate, 112) as int) = do.DateKey join DW_Northwind.dbo.Dim_Date dr on cast(convert(varchar(8), o.RequiredDate, 112) as int) = dr.DateKey join DW_Northwind.dbo.Dim_Date ds on cast(convert(varchar(8), o.ShippedDate, 112) as int) = ds.DateKey ) t
The columns are selected in the OLE DB Source editor, with an additional derived column casting the hash to a string
The warehouse source selects the identical columns from the fact table only. Each column is prefixed with db_ to provide uniqueness in the merge join
Before we can merge the 2 sources, both have to be sorted in order of the primary key of the source (OrderId, ProductId / Key). This sorting is available under the Advanced Editor or each source
The OrderId should have the SortKeyPostion of 1 and the ProductKey 2
Note: The sorting must be in place for both the staging and warehouse data sources.
The Merge Join task now merges the sorted outputs, joining on the OrderId and ProductKey.
This is where the hashing comes into play, detecting whether a record has changed. New and Old records are also detected, and the flow routed appropriately.
The splits are, with reference to the fact table
After the conditional split, each of the commands and the insert destination are self explanatory, with screenshots below
The key aspect to this pattern is the correct configuration of the staging and warehouse data sources, they should
With merge join dependencies correctly configured, everything else falls into place.
git clone https://github.com/mindfulsoftware/SSISMasterChildPattern.git