I am importing text files into SQl using the Transform Data Task. I need to
validate some information by checking if a value in the text file (i.e.
EmpID) exists in an existing table (Emp). I am trying to use a lookup in
the Transform Data Task (Lookups tab). What I can't figure out is how do I
reference the value from the text file in the SQL statement and how do I
know it the lookup is successful or not? Also, if the text file has multiple
fields to be validated, can I do this also? What I want to do is not import
the record if the lookup fails.

Thanks

Because you are bringing in rows from a text file I would be inclined to
move the whole lot into a staging table and use TSQL from there. Using
Lokoups forces you into Row*Row processing and that won't be funny if you
have a lot of data

Allan

"Tim Kelley" <tim.kelley (AT) iddonline (DOT) com> wrote

I agree with Allan on this one: staging tables make it much easier to do cross verification. TSQL is much easier to use that the lookups functionality in the Transform Data Task. This is how we do it as well (so I may be a little biased ;-)

Regards,
John Miceli