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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics