mruniqueid


I have a data flow two lookups components (call them lookup1 and lookup2). They both query the same relational table but with different values. Each has a single row result set containing one column and the each of the two columns is mapped to a corresponding package-level variable. The original data flow sequence had lookup1 executing after lookup 2. Each component redirects errors to a separate text file.

Lookup1 succeeds but lookup2 fails on every row which populates its error text file; however I can construct a sql query from the lookup2 values that returns the expected result.

If I reverse the sequence of components (lookup2 followed by lookup1) lookup2 still fails on every row. Whenever both lookups are present in the dataflow, lookup2 fails for every row and all its rows are redirected to the error text file

Now this is where it gets interesting. If I omit either lookup1 or lookup 2 from the data flow, it works. If the data flow contains lookup1 only the destination is populated. If the dataflow contains only lookup2 then no errors are written to the error text file, all lookups succeed, and the destination is populated.

I'm stumped. Is it possible that both lookups selecting from the same table could cause a problem Each works independently, but when both are together in the data flow, lookup2 fails for every row. I've been over the configuration and code a dozen times and am positive there are no errors; besides, lookup2 runs fine if lookup1 is excluded from the data flow.




Re: strange lookup behavior

Rafael Salas


 mruniqueid wrote:

I have a data flow two lookups components (call them lookup1 and lookup2).  They both query the same relational table but with different values.  Each has a single row result set containing one column and the each of the two columns is mapped to a corresponding package-level variable.  The original data flow sequence had lookup1 executing after lookup 2.  Each component redirects errors to a separate text file.

I am lost here. How  can you 'mapped to a corresponding package-level variable' within a Looup transform

 mruniqueid wrote:

Lookup1 succeeds but lookup2 fails on every row which populates its error text file; however I can construct a sql query from the lookup2 values that returns the expected result.

It seems like the problem is the Lookup 2; it should be interesting to see the error (truncation, not match ) that it throws. Also please provide more details on how exactly you are seting things up inside of the Lookups.







Re: strange lookup behavior

mruniqueid

You're right, I got confused. There are two package-level variables, one used by lookup1 and the other used by lookup2, that are added as new columns in one derived column component upstream from both lookups. These new columns are used in the lookup as input columns to the mappings.




Re: strange lookup behavior

mruniqueid

The point is, lookup2 succeeds when lookup1 is removed from the data transformation. It's only when lookup1 is present that lookup2 fails, and it doesn't matter which comes before the other, it's always lookup2 that has all its rows redirected to the error output file.

Another point that may be relevant is both lookups use the same connection; could that be a problem





Re: strange lookup behavior

Rafael Salas

You can use the same connection manager in as many components, including lookups, as you want with no problem.

How are the error outputs of the lookup component configured By default Lookup components try the no matches as errors. That is why I was asking about the specific error you received. Please provide the error message and more details about the Lookup components settings.






Re: strange lookup behavior

mruniqueid

Lookup Output / Error is set to "Redirect row" to a flat file destination. The ErrorCode = -1071607778 which translates to "No results were found for your query" and the ErrorColumn = 0 meaning the row in general (no specific column).



Re: strange lookup behavior

Rafael Salas

it seems like the query in the lookup column does not retrive any rows...did you check that so you get any row when yuo use the preview button or if you run the same query in the a SQL query tool




Re: strange lookup behavior

mruniqueid

There are three very relevant observations here:

(1) when you execute lookup2 in sql query it retrieves rows as expected

(2) lookup2 does not retrieve any rows when lookup1 is present in the dataflow

(3) when you remove lookup1 from the data flow, lookup2 succeeds

Point (3) is the most pertinent of all. It seems there is some strange interaction between lookup1 and lookup2 that prevents lookup2 from succeeding when lookup1 is also present in the data flow. As I mentioned above both lookups select from the same table. I wonder if there is something internal going on in SSIS that causes this problem

In the meantime I also tried a multicast whose outputs are lookup1 and lookup2 (instead of the two lookups following each other sequentially), as well as a separate connection for lookup2 (different than the connection used by lookup1). Neither variation made any difference.





Re: strange lookup behavior

mruniqueid

More info: I deleted lookup1 (the one that originally worked) and copied and pasted lookup2 as lookup1 back into the data flow. I made the necessary mapping changes, and now lookup2 works but lookup1 fails (it was the other way around originally - lookup1 worked and lookup2 failed).

Just as before, if I delete the lookup that works (lookup2 in this case), the one that wasn't then works correctly.

This may be relevant: as mentioned above both lookup1 and lookup2 select from the same table, but moreover, they the same column. However, in both cases, that same column is added as a new column in the data flow with different aliase names.

So it looks like the first lookup to be created works and lookups cloned from that first one don't. This is sure looking like a bug to me.





Re: strange lookup behavior

Phil Brammer

mruniqueid wrote:

More info: I deleted lookup1 (the one that originally worked) and copied and pasted lookup2 as lookup1 back into the data flow.



Have you tried adding a NEW lookup instead of copying and pasting There could be some problems with respect to the unique GUIDs that end up matching on the lookups that are copied.






Re: strange lookup behavior

mruniqueid

Phil, I think you're on to something there. I re-created the entire package from scratch and it worked. After reading your suggestion I went back to the original package, deleted the lookups, re-created them, and now it works too. No more copy & paste for me.

Thanks - Dana Reed