Be careful with string comparisons in SSIS

  • [アーティクル]

I have worked a lot with SSIS the last few months, and in every project with SSIS I
come across the fact that SSIS treats strings with regards to upper and
lowercase.

Today SQL Server databases are in nearly 99% of all cases installed as
case-insensitive. So you get your data to import from somewhere, directly from
a database, by Excel Files, by csv-Files, and you just import to SQL Server.
During import to your data warehouse there are some lookups necessary, to look
for IDs in referential tables etc. All the import stuff on your DWH works fine,
for month, and then, suddenly, peng! The job failed last night. After hours of
investigation you found out that in one of the imports a string that you use
for a lookup task is written in different upper and lowercase than before, and
this crashes your lookup task, which is configured to fail the component if a
no lookup match happens.

So, my hint is: always compare strings in lowercase (or Uppercase, will also work)

in SSIS if you cannot make sure that

the upper and lowercase on the important lookup columns always comes in the
right case. Just imagine, your data comes from UNIX, so upper and lower case is
really a difference there, but in your system, on SQL Server, case insensitive,
those strings are the same.

What I do in SSIS: I add all strings that I need for comparison in a derived column
task to lower(column) in addition to the original column. When doing comparisons,
i.e. in a lookup task, I do not use the reference table as data source, I
always write a query, and make the lookup strings lower(Column) in the
recordset. Then I compare the derived lower column with the lower column from
the lookup. And, voila, the lookup always works, regardless of what comes from
the outside world into your DWH :-)

Comments

  • Anonymous
    September 11, 2011
    Hi Wolfgang Thanks for your post and the reminder that occasionally case CAN make a difference. Is there any reason in particular that you're specifying that the comparison should be in lowercase specifically, as opposed to uppercase? Thanks Andrew

  • Anonymous
    September 11, 2011
    Andrew, you can also convert to uppercase, of course. The lowercase is just a favour of me. So, convert both string to compare to lowercase OR uppercase, both will work.