One of the most frustrating learning curves for a DBA going to SSIS is the case sensitivity of the environment. Many developers are quite used to this due to option explicit .NET programming languages. The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you're not careful.

One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit. To resolve this, you may have to use an UPPER or LOWER function to make everything upper or lower case. You can also create a new column in the data flow that has the data as UPPER by the use of a Derived Column Transform. There are many options there keep in mind. In the Lookup Transform, you can do upper case the data by changing the SQL query as shown below if the City column is the one where the comparison is happening.

The expression language is also case sensitive. Let's take the conditional logic as shown below:

STATE == "FL" ? "Florida" : STATE

If the state of "Fl" comes through, it will not qualify to be changed to "Florida". Instead, you must make a like comparison as shown below:

UPPER(STATE) == "FL" ? "Florida" : STATE

Additionally, a more obvious case sensitive issue is with variables. All variables, whether in expressions or a script component are case sensitive.

-- Brian Knight