I have worked a lot with SSIS the last few months, and in every project with SSIS Icome across the fact that SSIS treats strings with regards to upper andlowercase.
Today SQL Server databases are in nearly 99% of all cases installed ascase-insensitive. So you get your data to import from somewhere, directly froma 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 lookfor 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 ofinvestigation you found out that in one of the imports a string that you usefor a lookup task is written in different upper and lowercase than before, andthis crashes your lookup task, which is configured to fail the component if ano 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 theright case. Just imagine, your data comes from UNIX, so upper and lower case isreally 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 columntask 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, Ialways write a query, and make the lookup strings lower(Column) in therecordset. Then I compare the derived lower column with the lower column fromthe lookup. And, voila, the lookup always works, regardless of what comes fromthe outside world into your DWH :-)