August 21, 2008 at 11:42 am
Hi all,
Just wondering what's the best way to do this. I have a flat file source (.csv file) object which is pretty much just a lookup table for other objects in the package. So for example it looks like:
Specialty_ID, Description
1, General Dentistry
2, Endodontics
6, Oral Surgery
etc.
There are multiple objects in my package that need to cross reference the ID to get the name. Now I know I can just use the multicast to make copies of this .csv source object then use a merge join on each object to get this, but it seems like there should be a better way to do it than that.
I thought maybe the lookup task would be perfect, but it only uses oledb sources. Is there a way to maybe do look ups on objects already in the package? I really just want to have my flat file source object in there once, then just have the other objects do look ups on this. Is this possible?
Thanks,
Strick
August 21, 2008 at 12:17 pm
Not really.
In SSIS 2008, you have an option to add to a lookup cache at runtime, so you could do what you are suggesting. In SSIS 2005, the easiest solution will probably be to load your data into a table and then use the table in subsequent components via a lookup.
You could use a temp table if you use a connection manager with RetainSameConnection set to True (keeping the connection open for the duration of your package) but it may be more effort than it is worth.
August 21, 2008 at 6:00 pm
Perhaps you can loads the file into an object variable at the start of the package and use the variable from then onwards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply