how to do validations using lookups if the DOB is null and not null
I have a source and destination table.before inscerting a record into the destination I need to check whether it is new or old.I do this by customer key.
when the record is new I have certain validations to be done with the target table.the validations are
1)check whether name+DOB+phoneno(this record is from source) is matching with the target table
2)if DOB is null then I need to have name+phone as my matching criteria with the target.
pls help me on this.fuzailrashid
November 23rd, 2010 9:37am
Basically, it sounds like you will need a Lookup that tries to match on Name, DOB, and PHone. Set the Error Configuration to "Re-direct to No-Match Output". Take the No-Match output and do another Lookup on Name and Phone. In the end, take all the ones that
have been matched and bring them together in a UNION ALL, and send that to an OLE DB Command that does the UPDATING. Anyone that does NOT get matched is new (No-Match from the second Lookup), so send those to an OLE DB Destination that does the inserts.
Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
November 23rd, 2010 9:54am
As I always say if both the tables are in the same db just modify the OLEDB SOurce query to join with the other table and get the data that you want.
Tweet me..
November 23rd, 2010 12:15pm
Else modify the source query by addidn another column like: name+COALESCE(DOB,"")+phoneno
Now use this new column for ur lookup.
Tweet me..
Free Windows Admin Tool Kit Click here and download it now
November 23rd, 2010 12:19pm
You can use lookup and conditional split together.
sourceName=targetname && isnull(sourcedob)?" ":sourcedob=isnull(targetdob)?" ":targetdob && sourcephone=targetphone
November 23rd, 2010 12:33pm
hi Todd,
ur suggestion looks fine.
But in source phone number comes in eight coulmns but in destination it is as one column.
how to compare this numbers with the destination table.
the destination table phone structure is like this
custid phoneno
1 1234
1 1567
1 3667
source table structure
custid phoneno1 phoneno2 phoneno3 .....
1 1234 1567
how to join this data in a single temp table .
and another concern is where i need to check the DOB is nul or not .
as per ur suggestion if we have combination of name+DOB and in source if the DOB is null or 1900-01-01 then I need to skip the DOB check and i need to have name+phoneno as my matching criteria(where I need to do this )
help me on thisfuzailrashid
Free Windows Admin Tool Kit Click here and download it now
November 23rd, 2010 11:30pm