SQL Server 2005 has been a part of my warehouse work for the last 3-4 years or so. Before that I used to do almost only Oracle as a target database. Now, I do both. I general I have not run into many bugs in SQL Server but yesterday I found a rather annoying bug.
I came to a customer site that was running an existing SSIS package which wasn't raising errors when it should. We had a data source connected to a SQL Server DB and it was supposed to simply read the 6000 or so rows that were in it (a view) and populate another table with those same 6000 rows with some data manipulation on the way. The problem was that only 144 rows were being read and no error was raised, all looked normal.
Now, this wasn't good.
Looking into the matter I saw that the data source was actually a view and when issuing the same statement from Mgmt Studio I retrieved 144 rows that were OK but then it raised an error since the other rows failed a cast of varchar to smalldatetime.
So, an error was being raised by the DB engine that was not being caught by SSIS! Not good, not good and really not good!
To cut a long story short the problem eventually boiled down to the datasource that was using a SQL Server OLE DB connection. Changing it to SQL Server native client solved the problem. What concerns me is that there could be tons of places using this driver and silently loosing errors.
Which could be a good thing? Ignorance is bliss.