Ok, so it turns my http://blogs.msdn.com/mattm/archive/2009/08/14/no-new-features-in-for-integration-services-in-sql-server-2008-r2.aspx wasn’t entirely correct.
SSIS does have one new feature which appears in the http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx . 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 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx 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 http://www.microsoft.com/windowsazure/sqlazure/ . 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 http://connect.microsoft.com/ before the final release.
