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'