Is 0.5 = 0.5 in SSIS?

Today I had an issue which was causing some peculiar results in my ETL. After approximately 1 hour of trying to find the exact problem, I managed to narrow it down to a Sort task. The Data Viewer which I put immediately before that task showed me:

ColumnA              ColumnB
BBHI                     0.5
LLHR                     0.5

The Sort did:

  1. Sort by ColumnB in Descending order
  2. Sort by ColumnA in Ascending order

I was surprised to see that the output was:

ColumnA              ColumnB
LLHR                     0.5
BBHI                     0.5

After trying to reverse the sort order on the two columns (first ColumnA and then ColumnB), I noticed that ColumnB changes the order of the two rows. The type of that column was Double Precision Float in SSIS and float in SQL Server. Since SQL Server also showed 0.5 for each column, I did not expect the reason for my wrong results to be the floating point number. And I was wrong. Clearly the 0.5 was not exactly 0.5 in one of the two columns. Luckily, I got advised by my client that if I multiply the number by 10,000,000,000 and then round to whole number, I would not have issues with losing any information. My fix was simple – a derived column – ColumnB_Int which did exactly that:

 (DT_I8)Round((ColumnB*10000000000.0),0).

Then I used that column in the sort instead of the original one. This did the trick.

Advertisements