November 28, 2007 at 5:50 am
I'm just getting started with SSIS and need pointing in the right direction with my first attempt to copy columns from one table to another table in a different database but with the added requirement to fill one of the column values in the destination table with the result from a lookup.
So far, I have one OLE DB Source object linked to one OLE DB Destination in 'Data Flow'. I've configured the column transforms and these appear ok. However, the problem is that I can't see how to setup the Lookup. I have added a lookup transform object to the Data Flow space, linked from the appropriate OLE DB Source. The simple sql query in the lookup returns the correct value when previewed. If I try to connect the output of this lookup object with the same OLE DB Destination that is the output from the first column transform, I get a warning message : -
"Cannot create connector. The destination component does not have any available inputs for use in creating a path"
There is column in the destination table available but I don't know how to direct the lookup output to it. I can see this available column by looking at the OLE Destination object properties and clicking 'Mappings'. The other columns are showing the links in the transformation with the destination column intended for the lookup output is not showing any links (as expected).
How do I linkup the output from the lookup object to the OLE Destination object? Is it ok to have two inputs into the OLE Destination object (ie. 1. the output from the OLE DB Source; and 2. The output from the lookup object)?
Thanks in advance,
Clive
November 28, 2007 at 7:21 am
Is this how your package looks?
OLEDB Source -> Lookup -> Destination
It should.
The lookup works differently than DTS. I have had to hook up both the good and error output of a lookup to get all the rows as well. This was in a case where the lookup only matched 2 rows so the unmatched rows were sent out the error path.
OLEDB Source -> Lookup =>> Union -> Destination
You may need a derived column task in there as well.
Norman
DTS Package Search
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply