Something new for SSIS in SQL Server 2008 R2 November CTP

Something new for SSIS in SQL Server 2008 R2 November CTP

  • Comments 1

Ok, so it turns my previous post about R2 wasn’t entirely correct.

SSIS does have one new feature which appears in the latest SQL Server 2008 R2 CTP. If you open up the ADO.NET Destination UI, you’ll notice a new “Use Bulk Insert when Possible” check box.

image

Previously, the ADO.NET Destination did all of its inserts row by row (we do some batching internally, which is why the component has a BatchSize property, but the underlying ADO.NET provider will always do single row inserts). With this new feature enabled, SSIS will use a bulk insert interface (like enabling “FastLoad” for OLEDB Destination). Unfortunately, there isn’t a generic Bulk Load interface for ADO.NET, so this functionality is currently only supported by SQL Server (through the SqlBulkCopy API). Hopefully we can extend support to other ADO.NET providers in the future.

The main reason for implementing this functionality was to improve our support for SQL Azure. As you might already know, ADO.NET is the primary way to communicate with SQL Azure, and adding support for SqlBulkCopy greatly increased the transfer speed. It also speeds up things when you’re working with regular SQL Server systems -- although OLEDB with FastLoad is still the preferred way of doing SQL data loads.

Be sure to try it out and provide feedback before the final release.

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
Page 1 of 1 (1 items)