October 8, 2009 at 2:25 pm
I have a package that reads from table xyz (ole db source).
This does a select from table xyz.
The db source conects to a lookup. The lookup does the join on the reference table. On the column table, the columns I need to come back are checked off (value a and value b).
This works. The problem is that I need to do many lookups. I need one ole source, many lookups and one destination. I cannot connect more than one lookup to 1 destination.
October 8, 2009 at 9:37 pm
I'm not quite sure what problem you're describing here. Are you trying to do many lookups on the same table? If you're using SSIS 2008, you can use the Cache Transform to avoid multiple round trips to the server.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 8, 2009 at 11:03 pm
Well as you are using one and the same destination you can use either Merge transformation, or Union all transformation.
October 9, 2009 at 7:20 am
I am trying to do more than one lookup on one table.
October 9, 2009 at 7:37 am
There shouldn't be any reason that you can't do this, unless you have an issue of contention and you're getting deadlocks or excessive locking. When you say you cannot do this, is there an error message you're receiving when you execute the package?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 9, 2009 at 9:22 am
I have one source doing a select of table xyz.
This is linked to one lookup.
I have one destination. All data with value from lookup is inserted into the destination.
If I repeat this for the second lookup, I get another insert.
How do I do, one source, many lookups, one destination. I need to do many lookups to many different tables.
October 9, 2009 at 9:33 am
You can add more than 1 Lookup to a data flow.
Simply connect the matched output of your 1st Lookup to your 2nd Lookup, etc.
October 9, 2009 at 10:39 am
Thank you. That worked.
Aside issue on lookup.
My lookup table (reference table) has code 1, 2, 3, 4.
My main table has cod 1, 2, 3. "4" not it in. Lookup has
a problem with this and errors out.
October 9, 2009 at 11:16 am
Are you sure that problem isn't the other way around?
There are 2 things you can to to handle unmatched record errors in a Lookup. Ignore them, or redirect the rows to a different stream. Choose what you want to do by clicking the Configure Error Output button in the Lookup, and selecting a choice from the drop-down under the Lookup Output Error column.
October 9, 2009 at 12:10 pm
Thank you for your help. I changed the fail to ignore and worked.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply