Just had the occasion to look at SSIS Sources and Destinations in SQL Server 2012 RC0. After reading about the stated direction to use ODBC for SQL Server/relational data access over OLE DB and writing up my own blog post about it, I wondered how long it would be before a native ODBC source and destination appeared in SSIS. Didn't have to wait long…its in RC0.
Following the dialogs for these components (and the new ODBC Connection Manager), they seem to use native ODBC, not the OdbcDataProvider in ADO.NET as in previous versions. The SSIS team blog mentions that they are third-party components (from Attunity). I wonder if this will be the case post-SQL Server 2012.
Although the Source Assistant and Destination Assistant appear to route SQL Server connectivity through the SQL Native Client OLE DB provider for now, and the SQL Server Destination still exists, folks that are using SQL Server with SSIS might give the ODBC Source and Destination a try. Check out the speed and functionality vs. the OLE DB Source/Destination. ODBC has been named the supported "native" API for relational data in the future.
@bobbeauch