Problem:
I have a lookup transformation in one of my SSIS packages, used it to get ID of an employee record in a dimension table. However my problem is that some of the source data has employee names in Different case, which is not matching with the Case that Stored in the Dimension Table.
For Example:
Source Table Employee Name is "KUmar", but in the Dimension table, It is Stored as "Kumar"
The Lookup is failing for this Scenario wherever the case of the Employee Name are not similar
So, How can I get rid of this issue, Is there a way to make a lookup transformation ignore case?
Solution:
There is No option to change the transformation be case-insensitive directly. But We can handle this issue in two ways:
Option 1: If the data need not to lookup based on the case Then Convert both in to similar (UPPER (Or) lower) case before do the Lookup. This will not omit any records, you will get matched records for all
Option 2: Set the CacheType property to Handle the case Issue.
Full Cache: This option is case sensitive by default.
Partial and No-cache: This option uses the Collation setting of the database (or table) to handle case.
Set the CacheType property as Partial or None, Now row by row comparisons will be taken care by SQL Server and not by the SSIS lookup component.
Hope This Helps!
I have a lookup transformation in one of my SSIS packages, used it to get ID of an employee record in a dimension table. However my problem is that some of the source data has employee names in Different case, which is not matching with the Case that Stored in the Dimension Table.
For Example:
Source Table Employee Name is "KUmar", but in the Dimension table, It is Stored as "Kumar"
The Lookup is failing for this Scenario wherever the case of the Employee Name are not similar
Solution:
There is No option to change the transformation be case-insensitive directly. But We can handle this issue in two ways:
Option 1: If the data need not to lookup based on the case Then Convert both in to similar (UPPER (Or) lower) case before do the Lookup. This will not omit any records, you will get matched records for all
Option 2: Set the CacheType property to Handle the case Issue.
Full Cache: This option is case sensitive by default.
Partial and No-cache: This option uses the Collation setting of the database (or table) to handle case.
Set the CacheType property as Partial or None, Now row by row comparisons will be taken care by SQL Server and not by the SSIS lookup component.
Hope This Helps!
No comments:
Post a Comment