Podczas 58 spotkania PLSSUG, które odbyło się 9 lutego w warszawskiej siedzibie Microsoft, miałem przyjemność poprowadzić sesję na temat Slowly changing dimension w SSIS. Zgodnie z obietnicą zamieszczam link do mojej prezentacji a także fragment kodu, który wzbudził zainteresowanie.
MERGE INTO SCD_DST.dbo.DimPanels_2 p2 USING ( SELECT PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus FROM SCD_SRC.dbo.DimPanels ) src ON src.PanelNumber = p2.PanelNumber WHEN MATCHED AND ( p2.CityName <> src.CityName OR p2.FormatCode <> src.FormatCode OR p2.OwnerType <> src.OwnerType OR p2.OwnerName <> src.OwnerName OR p2.Address <> src.Address OR p2.District <> src.District OR p2.PostalCode <> src.PostalCode OR p2.N <> src.N OR p2.E <> src.E OR p2.Azimuth <> src.Azimuth ) THEN UPDATE SET CityName = src.CityName, FormatCode = src.FormatCode, OwnerType = src.OwnerType, OwnerName = src.OwnerName, Address = src.Address, District = src.District, PostalCode = src.PostalCode, N = src.N, E = src.E, Azimuth = src.Azimuth WHEN NOT MATCHED BY TARGET THEN INSERT ( PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus, DateFrom, IsCurrent ) VALUES ( PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus, GETDATE(), 1 ) ; INSERT INTO SCD_DST.dbo.DimPanels_2 ( PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus, DateFrom, DateTo, IsCurrent ) SELECT PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus, DateFrom, DateTo, IsCurrent FROM ( MERGE INTO SCD_DST.dbo.DimPanels_2 p2 USING ( SELECT PanelNumber, CityName, FormatCode, OwnerType, OwnerName, Address, SalesAddress, District, PostalCode, Light, N, E, Azimuth, Inak, Znus FROM SCD_SRC.dbo.DimPanels ) src ON src.PanelNumber = p2.PanelNumber WHEN MATCHED AND p2.IsCurrent = 1 AND ( p2.Inak <> src.Inak OR p2.Znus <> src.Znus OR p2.SalesAddress <> src.SalesAddress ) THEN UPDATE SET DateTo = DATEADD(DAY, -1, GETDATE()), IsCurrent = 0 OUTPUT $ACTION Action_Merge, src.PanelNumber, src.CityName, src.FormatCode, src.OwnerType, src.OwnerName, src.Address, src.SalesAddress, src.District, src.PostalCode, src.Light, src.N, src.E, src.Azimuth, src.Inak, src.Znus, NULL AS DateTo, GETDATE() AS DateFrom, 1 AS IsCurrent ) AS SCD_OUT WHERE SCD_OUT.Action_Merge = 'UPDATE'