As you might now I recently migrated part of our database systems to SQL Server 2005. Most went perfect. just like expected. I have however discovered a very troubling fact. A few of our SSIS data flow tasks gave us corrupt data.
The strange thing however was that it doesn’t happen every single time. It seems to occuring when the system is under extreem load. What happens is instead of adding the values that are in the source table it added the number 0 everywhere.
To this day I still don’t exactly know what causes the problem, but it seems to be related to the memory buffer. In combination with SQL 2000 being the source and SQL 2005 being destination.