July 21, 2009 at 2:05 pm
Hi all,
I have a package where I have a data flow task that takes data from a table in one database and puts it in a table in another database. The problem is that the old table has a couple of columns called Status and Type. These are codes, not IDs. The new table will have the IDs for these, called StatusId and TypeId. The problem I'm having is with the second Lookup. The first one seems to execute fine (number of rows is correct), but the package crashes on the second Lookup.
Here's how the flow looks like in the Data Flow tab:
1. OLE DB Source
2. Lookup (from Status)
3. Lookup (from Type)
4. OLE DB Destination
Am I doing something wrong? Do I need another step between the first Lookup and the second Lookup? I can't find an example anywhere of two Lookups in one Data Flow task.
Thanks
July 21, 2009 at 2:31 pm
Your data flow looks fine to me. Are you sure that the failure is not because the Type value that the lookup is searching for does not exist? What is your Error Output configured for on your Type Lookup?
July 21, 2009 at 2:39 pm
Right! That was the exact path I was thinking too. He might put a data reader on the pipeline between the lookups and see the data flowing and perhaps "eyeball" a weird bit of data.
CEWII
July 21, 2009 at 2:44 pm
I checked all the type code values and they exist. I checked for nulls and empty spaces and there aren't any of those, either. I tried configuring the error output to ignore the error and the package ran, however the TypeId column was blank. The StatusId column was correctly populated. Not sure where to go from here.
July 21, 2009 at 2:47 pm
What exactly is the error it is giving you in the Output window?
CEWII
July 22, 2009 at 7:40 am
[Lookup 1 [730]] Error: Row yielded no match during lookup.
[Lookup 1 [730]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup 1" (730)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (732)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Not sure why I'm getting this error, because I already checked the data and there's nothing strange in there, nor any nulls or empty spaces.
July 22, 2009 at 8:19 am
OK, I figured it out.
One of the type codes is 2 characters long so I had to make the new Type table's type code a char(2). Even though none of the types used by my lookup (a subset of the data in the Types table) have values with a 2 character length, the Lookup fails. I tested this by truncating the type code to 1 character, and changing the type code column to be a char(1) and the package worked. Go figure!
Thanks for everyone's help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply