At the moment I’m on a project which involves SQL Server Integration Services 2008. I had experience with DTS packages back in SQL Server 2000, but things have changed quite a bit.
After some reading and trying I built a new package. It was quite easy, reading data from one table, adding a column and writing it to another table. I used the Ole DB data flow source and because the destination was a SQL Server I decided to use the SQL Server destination data flow.
After a few runs I found out that the data in my destination table was malformed. Some fields contained correct data, but most were wrong. It looked like the data was shifted in one way or another.
Adding debugging to the package showed me that the problem had to be in the SQL Server destination data flow.
Some trial and error work didn’t result in a solution so I finally replaced the SQL Server destination data flow with the Ole DB destination data flow. It turned out to do the trick!
I’m not sure why SSIS behaved like this, because the data entering the data flow component was ok.