SSIS Lookup transformation with ignore case

Hi experts,

I use the lookup task in SSIS. But the task sarches for exact matches.
I need to make a lookup which ignores cases.
So I tried to change the generated SQL-statement using the modify box in the advanced tab.
I tried to add the LOWER() function to the statement but an error was returned

Any suggestions?
select * from
	(SELECT a.name, k.PLZ,a.vorname,  k.name AS kundenname, k.Nummer , kg.SalesPersonCode
FROM kansprechp AS a JOIN Kunden AS k ON a.KundenCode = k.Code JOIN KundenGr AS kg on k.GrCode = kg.GrCode) as refTable
where LOWER([refTable].[name]) = LOWER(?) and [refTable].[PLZ] = ?

Open in new window

arthrexAsked:
Who is Participating?
 
HoggZillaConnect With a Mentor Commented:
I suggest you delete the lookup component and create a new one, I have been suprised many times to find this as a solution.

Next, add the LOWER for a.name to the SQL on the Reference Table tab. This might fix it for you, if not add create another column in your source query that is a lower case for your paramenter.
0
 
HoggZillaCommented:
There is nothing wrong with your Syntax, the Lower function will work as you have written. What is the exact error. Are you selecting against a MSSQL db? Do your variable datatypes match the columns in the where clause?
0
 
arthrexAuthor Commented:
Thanks for your reply.

The datatypes have to match, because without the Lower() function it works.
Attached you see the exact statement and the error messages.

Thanks
sqlStatement.GIF
errormessage.GIF
0
 
arthrexAuthor Commented:
Renewing the shape didn't work. SSIS doesn't seem to support the lower() function for the parameters in the statement (lower(?))
So adding a new column solved the problem. Thanks for the workaround.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.