Hi guys,
I would like to share an experience with you when I was working on 1 SSIS related issue.

Scenario was:
I have to exclude some records from Table A by matching some keys of Table B.

 Table A

 Table B

 What is expected was – Table A’s following records:

 So, only records of Table A should be used which are not matching with Table B.
It was a simple correlated T-SQL query. But I was facing some issue.

What was happening:

When I simply query following, I could see results. I mean results, where CustomerCode is ‘CX1000’
Select * From Table A;

When I do following query, I do not get any results. This was very surprised for me.
Select * From Table A Where CustomerCode = ‘CX1000’;

Then I tried following query, and I found required results.
Select * From Table A Where CustomerCode like ‘%CX1000%’;

Then I suspect something hidden/not visible information after ‘CX1000’ value. and I tried following to confirm. and I got results. And there I found my Ghost 🙂
Select * From Table A Where CustomerCode like ‘%CX1000%’ + Char(13);

The issue was:
Table A was filled by an SSIS package from some CSV file. and this column (i.e. CustomerCode) was at the last position of the column row in file. So while loading of this Table A, carriage return was inserted in the last column 🙂 How … is not our concern right now.

What you should do if you encounter this issue:
1. First thing you should do is to fix your SSIS package. it should insert only values and of proper length and type.
2. If fixing SSIS is not possible or permitted (may be its not in our hands), then you should remove unnecessary characters from that column value which querying that Table. E.g. LEFT(LTRIM(RTRIM(CUSTOMERCODE)), 6)
3. If you want, you can find Char(13) or Char(10) values in your columns and update whole table by removing them. (I will post very good script for that later…)

Note: I would like to share a credit for this post with my good friend & colleague Santosh Joshi (His blog: http://microsoftbizintel.wordpress.com).

I hope this will help you somewhere.
Thanks, Khilit