Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nicklarge
Starting Member
40 Posts |
 Posted - 2011-08-15 : 15:01:03
|
| Hello SQLTeam,I have some data going downstream and need to use a lookup in order to see if the item currently exists. As you know, this is a pretty common task. There is a field coming downstream called Customer-Location Key. This is a string in the format number + '-' + number. The reason for this is beyond the scope of the question. Anyway, In the source I have the value sent as LTrim(RTrim([Customer-Location Key])), so I expect that it is trimmed as it works it's way downstream. When it hits the (Partial Cache) lookup, the query sent to the server is :exec sp_executesql N'select * from (select [Customer Key], [Customer-Location Key] from [dbo].[vwCustomerKeyFromStagedCustomerDimension_CustomerLocationKeyLookups]) [refTable]where LTrim(RTrim([refTable].[Customer-Location Key])) = @P1',N'@P1 char(50)','13509815-5497 'So, I have added a derived column transform to trim it, but it still has the additional spaces.I thought "OK, what about modifying the custom query to trim the value?" so I set about putting in the trim:select * from (select [Customer Key], [Customer-Location Key] from [dbo].[vwCustomerKeyFromStagedCustomerDimension_CustomerLocationKeyLookups]) [refTable]where LTrim(RTrim([refTable].[Customer-Location Key])) = LTrim(RTrim(?))but SSIS would not let me assign the parameter as it thought that the text was an error, most likely due to the fact that I am trying to trim a question-mark.My only other way around this was to create a script component that will send the sql as a count of the records matching the trimmed sqlparameter, and that works but is incredibly slow because it sends the sql for each item, acting as a non-cache lookup.This is incredibly frustrating, for such a common task like a lookup to not allow a trim around the question mark.I wonder if anyone has any ideas about how this can be resolved other than the methods that I have mentioned.Regards, Nick. |
|
|
|
|
|
|
|
SSIS Lookup issue