SSIS Lookup and Case Sensitivity

Here's a little trivia question for ya...

True of False...
The Lookup Component in SSIS is Case Sensitive.


Unfortunately, the answer is TRUE!!!!! I just found this out the hard way. For data-warehouse loading, this can be a major issue - just imagine all the DW records that are set to 'Unknown' simply because of a case mis-match. Be warned!

The best protection is to use the UPPER or LOWER functions on both the incoming data AND the field you are looking up against. That way you won't miss any combinations.

DataSet Field List is Empty in SSRS Report Designer

If you create a DataSet in an SSRS report by executing a SQL Server stored procedure, the DataSet fields list may sometimes be empty when you switch to the Layout tab. The reason for this is that the Report Designer tries to determine the fields in the result set without actually executing the stored procedure. By default it uses SET FMTONLY ON which returns the metadata of the result set without actually executing the stored procedure. If your stored procedure creates a temporary table, for instance, the fields list will be empty because with SET FMTONLY ON it never gets created since the stored procedure isn't actually executed.

The solution is to put SET FMTONLY OFF as the first line in your stored procedure.