April 21, 2014 at 8:53 am
Hi Friends,
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.
Is there any way to check whether the column name is in small case or upper case through query?
Thanks for the suggestions in advance.
April 21, 2014 at 9:08 am
You can use the UPPER() or LOWER() functions to force case for a comparison.
April 21, 2014 at 9:50 am
NO Steve, the problem is one field is IsSortSeqNo and another one named like ISSortSeqNo...
If you noticed, the second letter of the field name is upper and lower case.
So SSIS has problem in mapping when execute with different databases. I know we can change the filed name in database itself, but I am just curious, whether there is any way to check through scripts ? So that I would use the script in Execute SQL task...
April 21, 2014 at 10:16 am
You could still use UPPER or LOWER (or their equivalent in SSIS Expression) to compare.
What you'd need to do is alter the mapping in the package by looking to see if the case insensitive comparison was valid, then take the new value from the database and use that in the mapping.
It's a mess, for sure. I don't know if SSIS can compare mapping fields in a CI manner.
April 21, 2014 at 2:34 pm
If I understand the question correctly, you can use the UNICODE function to determine the case if the characters, even if the column/db is case insensitive.
😎
April 22, 2014 at 2:58 am
Yes Steve, It's mess...but I had no idea so I am just asking if we have any way to do that
April 22, 2014 at 2:59 am
Eirik, Could you please elaborate me ?
April 22, 2014 at 3:37 am
prakashr.r7 (4/22/2014)
Eirik, Could you please elaborate me ?
Firstly, the character code for upper and lower cases are different, even on a case insensitive system:
;WITH NB(N) AS (SELECT N FROM (VALUES (1),
(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NN(N))
,NUMS(N) AS (SELECT ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM NB N1,NB N2,NB N3)
SELECT
NM.N AS CHAR_CODE
,NCHAR(NM.N) COLLATE SQL_Latin1_General_CP1_CI_AS AS THE_CHARACTER
,UNICODE(NCHAR(NM.N) COLLATE SQL_Latin1_General_CP1_CI_AS) AS CHAR_UNICODE
FROM NUMS NM
WHERE NM.N < 256;
Secondly, you can use the COLLATION to do a case sensitive comparison on a case insensitive system:
DECLARE @STR_1 NVARCHAR(MAX) = N'ThisIsStringOne';
DECLARE @STR_2 NVARCHAR(MAX) = N'ThiSIsStringOne';
SELECT
CASE
WHEN @STR_1 COLLATE SQL_Latin1_General_CP1_CS_AS = @STR_2 COLLATE SQL_Latin1_General_CP1_CS_AS THEN 'THE SAME!'
ELSE 'NOT THE SAME!'
END AS STRING_COMPARISON
;
😎
April 23, 2014 at 2:48 am
Thank you Eirik....I really appreciate your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply