Will there be any further resultset to process if the lookup table in the lookup task is empty?
sagar
views:
126answers:
1
A:
It depends on how you have your Lookup transform configured. It also depends on which version of SSIS you're using. SSIS 2008 allows you to configure the lookup so that a failure either goes to the error output, or to an alternate success output.
In either case, if configured, the row would go to one of those two outputs. I do not know what would happen if you have no error or alternate output configured. I think you're probably correct.
If you're trying to fill in a value from the lookup table if a match is made, but to do nothing if there is no match, then you need to configure one of the other two outputs, then use a Union All transform to bring the two branches back together.
John Saunders
2009-06-30 19:59:12
i am using ssis 2005. There is no error output configured. So, I think the task works in the assumption that there is data in the lookup table, but infact there is none. So, I was thinking either the package fails at the lookup task or the package succeed with no data at all in the final destination table. PS: I cannot run the package, because another dev is working on it.
sagar
2009-06-30 20:08:31
@sagar, if you turn on logging of warnings, you may see warnings about there being no data.
John Saunders
2009-06-30 20:10:29
THanx john I look into it.
sagar
2009-06-30 20:22:28
What kinda join is lookup task anyway? is it an inner join?
sagar
2009-06-30 20:40:27
@sagar: it's not a join. The lookup transform (they're not called tasks inside of a data flow: sources, destinations and transforms) logically reads in the lookup table, then matches on each row, updating the output with the specified output columns. Remember you're in SSIS, not in the database engine.
John Saunders
2009-06-30 20:51:36
well if this is the case, then will you not consider for using sql command using joins instead of lookup transforms for a very large lookup table. Both of them do the same operation, dont they? I have used myself and seen people using joins instead of lookup transform for a very large lookup table.
sagar
2009-06-30 21:59:26
@Sagar: the lookup task is optimized for this function. I recommend you start with the lookup task. It's even faster and better in SQL Server 2008, as you can arrange for it to cache the set of rows it reads for the lookup table, and to keep the cached data on disk. The cached data can even be incrementally updated, with the result that a large lookup table may be maintained without a large request to the database.
John Saunders
2009-07-01 00:11:23