In SSIS when creating a Data Flow Task, you’ll usually want to have a destination for your data. From everything I’ve read, if your package is running on the same server as your destination, you’ll want to use SQL Server Destination as your destination type. If you’re on a different server, you’ll want to use OLE DB as your destination type and occasionally ADO.Net though ADO.Net is slower, having more levels of abstraction.
I developed my entire ETL using SQL Server Destinations as I knew I’d be running the package on the destination server. I tested it all on SQL Server 2008 SP2 and Windows Server 2003. When it came time for deployment to the production machine, I used SQL Server 2008 R2 on Windows Server 2008 and it simply doesn’t work. I switched over to OLE DB as my destination and it works fine. I’m looking forward to installing SP 2 for SQL Server 2008 R2 as this may have fixed the various problems I’ve mentioned so far, but we’re waiting for a few weeks to install the patch. When that’s done, I hope to test the various bugs I’ve found again and see if everything works. I’ll report the results here.
In the meantime, if you’re using SQL Server 2008 R2 on Windows Server 2008 and no patch for R2, use OLE DB as your destination. I didn’t notice any appreciable speed difference and it works quite well.