Sample SQL script below
Before checks -
Columns from Source
Address1 = 67 office
Address2 = NULL
Address3 = Reading road
Address4 = NULL
Address5 = NULL
Columns from destination
Address_1 = 67 office
Address_2 = NULL
Address_3 = Reading road
Address_4 = NULL
Address_5 = NULL
City_d = London
I used conditional split in SSIS to check for changes between the source and destination column for any checks between columns using the expression below in the conditional split –
((IsNull([Address1]) ? "?^$@" : [Address1]) != (IsNull([Address_1]) ? "?^$@" : [Address_1]))|| ((IsNull([Address2]) ? "?^$@" : [Address2]) != (IsNull([Address_2]) ? "?^$@" : [Address_2]))||
((IsNull([Address3]) ? "?^$@" : [Address3]) != (IsNull([Address_3]) ? "?^$@" : [Address_3]))||
((IsNull([Address4]) ? "?^$@" : [Address4]) != (IsNull([Address_4]) ? "?^$@" : [Address_4]))||
((IsNull([Address5]) ? "?^$@" : [Address5]) != (IsNull([Address_5]) ? "?^$@" : [Address_5]))
Which compares if any column values have changes since the last load –
If after using the conditional spilt and any of the address columns 1-5 have changed then the City column should be NULL.
For example-
After checks -
Columns from Source
Address1 = 68 office
Address2 = NULL
Address3 = Reading road
Address4 = NULL
Address5 = NULL
Columns from destination
Address_1 = 67 office
Address_2 = NULL
Address_3 = Reading road
Address_4 = NULL
Address_5 = NULL
City_d = London
When copied into the destination table the result would look like below –
Address_1 = 68 office
Address_2 = NULL
Address_3 = Reading road
Address_4 = NULL
Address_5 = NULL
City_d = NULL
The City column should be NULL because address1 does not match address_1.
Hope that helps.
Thanks
create table dbo.AddressSource
(Address1 varchar(50),
Address2 varchar(50),
Address3 varchar(50),
Address4 varchar(50),
Address5 varchar(50))
GO
Insert into dbo.AddressSource (
Address1,
Address2,
Address3,
Address4,
Address5
)
VALUES (
'67 office',
NULL,
'Reading road',
NULL,
NULL
)
Go
create table dbo.AddressDest
(Address_1 varchar(50),
Address_2 varchar(50),
Address_3 varchar(50),
Address_4 varchar(50),
Address_5 varchar(50),
City varchar(50),
WANTED_RESULTS varchar(200)) /* IF ANY OF THE ADDRESS1-5 LINE CHANGE or not change
and how it affects the CITY column*/
GO
--the result should only bring back one row- there two to show wanted results -
INSERT INTO AddressDest (Address_1,Address_2,Address_3,Address_4,Address_5,City,WANTED_RESULTS)
VALUES ('68 office',NULL,'Reading road',NULL,NULL,NULL,'one of the address column has changed the City is now NULL');
INSERT INTO AddressDest (Address_1,Address_2,Address_3,Address_4,Address_5,City,WANTED_RESULTS)
VALUES ('67 office',NULL,'Reading road',NULL,NULL,'London','nothing has changed in address1-5 the previous value will be used so London'
)