SSIS is Case Sensitive

Yes, SQL is not case sensitive but SSIS is. Recently we came across an issue which was very difficult to trace and find the root cause.

The requirement was to update the sales data for each product. The logic was working fine for all the records and data was getting updated properly except for couple of records.

We were getting list of products from a table and sales data for products from a different table. After enabling “Data Viewer” for both the blocks, we found that data is coming properly from both the tables. However after merge join, sales data was not being updated for 2 records. We also saw in Data Viewer that ProductCode is coming in UPPER CASE while getting products where as ProductCode is coming in LOWER CASE while getting sales data.

To fix this issue, while getting data from different tables we simply changed the SQL queries to get productcode(column on which we were performing JOIN operation) in UPPER CASE by using UPPER( ) function of SQL as below:

For getting product details, we used:

select UPPER(ProductCode) as [ProductCode], […rest of the columns…] from TableA

For getting sales details for product, we used:

select UPPER(ProductCode) as [ProductCode], Quantity, [.. rest of the columns..] from TableB

NOTE: The above queries are sample queries to demonstrate how we fixed the issue and not the actual queries.

After doing the above changes in the SSIS package, we executed the package again and after merge join we found in Data Viewer that the sales data got updated successfully for all the products.

Hope it helps !!