Something new for SSIS in SQL Server 2008 R2 November CTP

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.


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.

Comments (1)

  1. David Bridge says:

    I think you need to trackback to this article about the problems of 2008 forward compatibility on this feature.…/moving-ssis-packages-with-ado-net-destinations-between-2008-r2-and-2008

    or check my post on the same issue…/ssis-2008-and-ssis-2008-r2-are-the-same-or-are-they

    MS should create a hotfix for SQL 2008 so that the element relavant to this feature is ignored in 2008. Alternatively a 2008 R2 developerment environment (and 2012 too) should allow selective deployment targets the way that SSRS does.


Skip to main content