1

I'm having an issue with a SSIS Lookup component. My source table has a string value (XXX), and I need to know if it exists on another table. If I make a query like

select AAA from dest_table where AAA = XXX

I DO get a matching result. This value does exists in both tables. But when I use a Lookup component, it doesn't find any matches and returns NULL (and this leads to a crash in all the next steps of my package). My lookup query is:

select AAA, BBB from dest_table group by AAA, BBB

I need BBB value for following ETL processes. Plus, I added a Multicast component so I can lead all the results I got from the lookup to a testing table, and at the same time let the package flow continue its course. And this testing table only has null values inside (meaning lookup didn't find a match)

What can be the reason behind this?

Thanks in advance!

Neko069
  • 49
  • 1
  • 9
  • 1
    Is your lookup component using `SELECT * FROM table` as your query? What is the data type of the `value` column? What is the data type of your source column `XXX`? – billinkc Sep 24 '14 at 14:45
  • No, it's not. It uses a specific field as query, I only need to see if a specific value is in both tables. This value is a varchar (50) in both source and destination, and it contains codes like IDs. I just need to see if this value exists in the destination table, so not to insert duplicates. – Neko069 Sep 24 '14 at 14:52
  • 1
    Would you update your question with the actual query used in your lookup? Sanitize the names as needed. I'm trying to discern whether it's an issue with how you have your Lookup designed, a silent data conversion issue or something else. – billinkc Sep 24 '14 at 14:54
  • sure, no prob! done! – Neko069 Sep 24 '14 at 15:06
  • 1
    Have you checked that there is no white space at the end of your target column value? Also, what is the collation of the strings on your table? – N West Sep 24 '14 at 15:10
  • Yes, both columns in source and destination have an ltrim(rtrim(value)). Regarding collation, I'm not so sure, but tables are from different servers, so there may be different types of collation. Can that be a problem? – Neko069 Sep 24 '14 at 15:14
  • 1
    Can you give a few samples of the actual values? – AHiggins Sep 24 '14 at 15:17
  • 1
    Is it possible that the data you are looking up is being created in this process? I assume not, based on "crash in all the next steps". The source query looks like it's probably fine. Have you changed the [Caching mode](http://stackoverflow.com/questions/21994136/when-to-opt-for-caching-in-the-lookup-component-in-ssis)? Double click on the line coming into the Lookup Component and choose Metadata. What is the length of the AAA column? Oh and as referenced, lookups are case sensitive. Is it possible that you have a mismatch there? – billinkc Sep 24 '14 at 15:18
  • This value I'm looking up, is like an ID for people in my country, and they are numeric, with 11 digits, so they look something like '11223344556', as they are varchar type in both tables. Data is not being created on the go. It's just a simple process we created to see if package is inserting values that should not be repeated (as they're like IDs) Metadata for AAA column is correct AFAIK, it's DT_STR with a lenght of 50, code 1252. Finally, I don't believe I have a mismatch of 'case sensitiveness' as they're just numbers (although they're in a varchar type) – Neko069 Sep 24 '14 at 15:40
  • Is it really like that? I mean, It's hard for me to imagine this is actually happening. I thought a lookup component just looked for a value existing in another table, regardless of order...If order is a defining fact, is that why it returns NULL, although value exists in both tables?? – Neko069 Sep 24 '14 at 16:02
  • 1
    Look ups do not need to be ordered to succeed. If it is not a case sensitivity issue, I would look for other white space characters in either strings - tabs, NBSP, CRLF, etc. Also, try converting to a numeric type in both cases for the sake of testing it. – Mark Wojciechowicz Sep 24 '14 at 17:22
  • 1
    How is your lookup set to handle rows without matching entries--fail component, ignore, redirect to error or redirect to no match? – Ben Gribaudo Sep 24 '14 at 21:14
  • 2
    Check for whitespaces and make sure the case matches. Add for example ltrim, rtrim and upper to the lookup query (and same to source): select UPPER(LTRIM(RTRIM(AAA)))) as AAA from dest_table – Joost Sep 25 '14 at 06:02
  • Value in both source and destination is trimmed. Lookup is set to 'Ignore Failure', and everything else is working. In fact, lookup itself does not crash. It DOES work, but returns NULL (as if it didn't find any matching row, where clearly there is a row to match). – Neko069 Sep 25 '14 at 13:07
  • I'm taking a wild guess that maybe this has something to do with a lack of memory issue. It really has become a very big package with a lot of information to process. Is this possible? – Neko069 Sep 25 '14 at 13:09
  • UPDATE: I just tried the exact same logic, but instead of using a lookup, I did the raw query on a SQL Task component. And it worked! I made it return true if count(*) returned more than 0 rows, and it returns 1 (meaning it found a matching record). Really, this is getting frustrating... – Neko069 Sep 25 '14 at 13:14
  • 1
    Do you have access to run a SQL Profiler trace on the database server? If you can, you might try capturing the exact lookup query being executed by SSIS, including _exactly_ how it's specifying parameters, then running that query to see what it returns. – Ben Gribaudo Sep 25 '14 at 15:19
  • I'm not quite sure I'm able to do this, but of course will give it a try. Anyway, I'm bypassing this by creating the exact same query from the lookup, in a SQL Task. And amazingly, it works perfectly... – Neko069 Sep 26 '14 at 12:58
  • Ok guys, after several tries, I still cannot find out why is this happening. However, I'll be bypassing this with a simple SQL Task component, with the same query that lookup component has. This doesn't give any errors, don't know why...thanks anyways! – Neko069 Sep 30 '14 at 13:52

1 Answers1

0

Ok guys, after several tries, I still cannot find out why is this happening. However, I'll be bypassing this with a simple SQL Task component, with the same query that lookup component has. This doesn't give any errors, don't know why...thanks anyways!

Neko069
  • 49
  • 1
  • 9