Evil Kraig F (9/7/2012)
apache626 (9/7/2012)
I receive a flat file which I am importing with SSIS. The problem is the file does not contain a needed field(ssn). I have to query another table to find the ssn information. As an example, I receive a file with about 250 records. These records have firstname, lastname, and dob. I need to use this information to query the other table to find the SSN and add that to a new table. So for each record I must find the corresponding SSN located in the other table. Does anyone have a good idea about how to approach this?Thanks in advance.
I agree with OPC on this one, the lookup is the component you'll want for SSIS... however, this is inverted to expected row counts (something he mentioned). Lookup works best at many inbound rows to few checked, not few inbound rows to billions checked... because it has no indexing.
My recommendation with this few records being imported would be to complete your import and then immediately run a standard update T-SQL script against the data to review the index on your main table to add the SSN to the information.
I agree, if the destination of your incoming data and the location of the SSN are on the same instance. My pointer towards using the Lookup Transformation assumed the destination of your incoming data and the location of the SSN were on different instances, i.e. not reachable via T-SQL and excluding Linked Servers.
Without an index this will be a horribly painful operation either way. Make sure you've got a nonclustered index on the lookup table on DoB, First, Last and INCLUDE the SSN column so you have the tightest lookup available for your update.
If the data is on separate instances (i.e. proceeding with the Lookup Transformation) and you'll be searching a ton of data when doing the SSN lookup then I recommend disabling cache altogether and letting SSIS do the 250 sequential lookups. With that few lookups to do there is no sense in caching any of the table that carries the SSN if that table is large. Having an index on the SSN table as Craig mentioned to keep lookup time to a minimum will be critical to the overall performance of the Transformation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato