I need to validate my input rows. The row is valid if there exist some other input rows in the same table (I am importing data from excel and access). I'll give an example to make everything clear:
Input table boys has following columns:First_Name ,Surname and Date_of_birth.
Output table is Twin_Triple_More_Brothers. I would like to insert into this table only boys that surnames are equal and difference in date of birth is less then one day.
I was thinking about lookup component, but I cannot use it in that way (or I just do not know how).
Maybe someone has an idea how to do this?
Thanks for help.
You could try something like this...
-Send your source to a lookup with the lookup query on the surnames and the date of birth
-Send error rows of this lookup to another lookup with the lookup query on the surnames and the date of birth-1
-Send the successfull lookups from the two lookup components to a union all component
-Send the output of the union all component to your destination
|||Use a lookup to match on Surname. Then return Date_of_birth from the lookup table.
Next, hook up to a derived column to calculate the difference between the input Date_of_birth and the lookup Date_of_birth.
Hook that up to a conditional split, where you test the calculation from above. If it's within your parameters, send to the insert path, otherwise do nothing.|||But how can I lookup in input? I know how to search in other table, but I do not know how to search in input columns.
Again thanks for help.
|||
 Piotr Stapp wrote: |
| |
But how can I lookup in input? I know how to search in other table, but I do not know how to search in input columns.
Again thanks for help.
|
Create an initial data flow that simply takes your input and loads it to a table. Then in your second data flow, you can use an OLE DB source hooked up to that staging table, having a lookup pointing to the same table. You might have to work through the matching logic a bit, but it could work for you.|||So the only way is to use temporary table?
Maybe it is not as good as I think, but it is also not so bad.
|||You could do this in SQL, probably, but this isn't the forum for that... 
