September 6, 2011 at 8:33 am
Hi,
I am new to SSIS and practicing Lookup Transformation. I am trying implement the following query in the Lookup Transformation Override (Lookup Transformation Editor -> Advanced -> Modify the SQL Statement Option)
select * from (SELECT * from T_Test) [refTable]
where
CHARINDEX(?,[refTable].[FIRST_NAME],1) > 0
and CHARINDEX(?,[refTable].[MIDDLE_NAME],1) > 0
and CHARINDEX(?,[refTable].[LAST_NAME],1) > 0
and [refTable].[Street] = ?
and [refTable].[City] = ?
and [refTable].[State_Country] = ?
I am getting the following error:
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Argument data type int is invalid for argument 1 of charindex function."
The parameters are all mapped to character values. Can anyone please guide me where I am going wrong here?
Thanks,
Joice
September 6, 2011 at 8:47 am
Joice
Do you know what values are being passed to the CHARINDEX expressions? If they're numbers in character format, they might be getting interpreted as integers when the database engine parses the statement. Might be worth an explicit conversion - CHARINDEX(CAST(? AS varchar(6)),... for example.
John
September 6, 2011 at 9:08 am
Thanks for the reply John.
The values being passed as parameters (?) into the CHARINDEX Function are all character values. As per your advice, I did a CAST for these parameters.
Good News is the error is no longer there. :w00t:
Bad News is the CHARINDEX Function is not working as expected :blink:
CHARINDEX(CONVERT(VARCHAR(255),?),[refTable].[FIRST_NAME],1)
gives me 0 for all the incoming parameters whereas while cross-checking the value for one of the parameters manually, it gave me a non-zero value:
CHARINDEX('JOHN',[FIRST_NAME],1) returned 1
So the Lookup logic is not working as it should.
Regards,
Joice
September 7, 2011 at 1:58 am
Joice
I don't know what the problem is, then. The only thing I can think of is to set something up that writes the values of the parameters to a table so that you can inspect them. Maybe they have a leading or a trailing space that T-SQL ignores but SSIS doesn't? Are your parameter values in the same case as the values in the database?
John
September 7, 2011 at 6:41 am
Thanks for the reply John and sorry I couldn't get back on the issue yesterday. I have already tested the query in the Database end and it works. Somehow the Lookup override in SSIS is throwing things off. Maybe the Lookup SQL Override takes parameters differently.
I am not too confident in my ability with the Lookup Transformation in SSIS. Can you help me with any inputs for the Lookup Transformation?
Thanks again.
Joice
PS: If I am not posting in the right forum, please direct me to a proper one.
September 7, 2011 at 6:47 am
Somehow the Lookup override in SSIS is throwing things off. Maybe the Lookup SQL Override takes parameters differently.
That's the point I was trying to make, Joice - that SSIS and T-SQL may treat strings differently when comparing them. Have you ruled out the two things I suggested - leading or trailing spaces and upper/lower case?
John
September 7, 2011 at 8:23 am
Yes John. No leading and trailing spaces anywhere.
Here is the actual scenario:
DIM_DETAILS (Lookup Table)
-------------
First_Name | Last_Name | Middle_Name | Addr1 | State | Country | LNo
----------- ---------- ------------ ------ ------ -------- -----
James John Michael ABC St NY US K1998NY23
Mary Clarke James ABC St NY US K2010NY29
Now the source is an Excel File which has the values as follows:
Excel File (Source File)
----------------------
First_Name | Last_Name | Middle_Name | Addr1 | State | Country |
----------- ---------- ------------ ------ ------ --------
James John Michael ABC St NY US
J John M ABC St NY US
James J M ABC St NY US
James John M ABC St NY US
The Query will help me fetch the LNo column from the lookup table for each value coming from the source. As you can see the same person "James Michael John" has four variations in the source and for each record I need to get the LNo column from the lookup table.
I guess, I can do it with Fuzzy Lookups but doing with a complex Lookup override will help me understand the Lookup Transformation better. If you can guide me to a better solution, please let me know.
Thanks,
Joice
PS: The source file is cleaned for leading and trailing whitespaces before it reaches the Lookup transformation.
September 7, 2011 at 8:58 am
Joice
I'm afraid I don't have much experience of using the Lookup transformation. I've tried everything that was obvious - hopefully somebody else will be able to help you.
John
September 7, 2011 at 9:43 am
John,
Thanks for all the help you provided. If I get any helpful links regarding this I will post it down here.
Hope someone can help me in this. :ermm:
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply