Solved

SSIS Lookup transformation with ignore case

Posted on 2008-10-14
4
1,013 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:arthrex
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22715195
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
 

Author Comment

by:arthrex
ID: 22718543
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22719501
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
 

Author Comment

by:arthrex
ID: 22720874
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SPROC to look for existing record in passed table name 7 46
Nested cursor  in SQL 9 94
Linking a DMV to a database id/sql text in SQL server 2008 8 45
grouping logic 6 46
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now