Monday, September 12, 2011

SSIS offers a number of Data Flow destination components. When working with SQL Server databases as a destination there are two obvious choices.

OLE DB Destination

SQL Server Destination

Microsoft claims up to a 15% performance increase by using the SQL Server destination. Therefore it seems like a good choice. However, there is one rather large caveat. SSIS must be running on the same SQL Server as the destination database. It cannot write to a remote SQL Server.

That said, I have seen many solutions that have both SSIS and the destination database on the same server so it seems to make sense to use it in these situations. Personally, I choose not to. By using the SQL Server destination you're locking yourself into having to make a code change if you decide to split your SSIS and database instances across multiple servers. Having SSIS code dependent on a solution's hardware architecture is something I always try to avoid.