I am extremely impressed with Microsoft’s commitment to ODBC in SQL Server Integration Services 2012, and they were not kidding about aligning with ODBC for data access going forward. This is exciting for me since there have been a lot of data access choices from SSIS including OLEDB, ADO.NET and ODBC with various support depending on version (for example, 2005 supported ADO.NET sources, but not destinations).
Three reasons to love native ODBC components in SSIS 2012
SSIS was completely rewritten for ODBC data connectivity for which DataDirect is the worldwide leader and the specification was co-founded by our very own John Goodson. Below are the top 3 reasons I am excited about the 2012 release from my experience connecting enterprises from SSIS 2005 and 2008.
1. Get the best performance with ODBC source and destination components native to SSIS
It is generally known that Progress DataDirect Connect for ODBC drivers dramatically outperform competitive drivers. With previous releases of SSIS, there was a data access layer in between that can impact performance compared to other providers such as .NET or OLEDB. (2005 MSDASQL OLEDB to ODBC bridge and 2008 .NET ODBC data provider). However, based on some diagnostics I have captured and initial performance results, 2012 looks to be the fastest solution and is written natively in ODBC.
Nico Jacob’s Microsoft Technet video on “What’s new in SQL Server Integration Services 2012” shared that ODBC is becoming more and more important. One reason is because of SQL Azure and Microsoft is planning to drop the OLE DB interface in future versions.
Here are some slides from the above video that show ODBC performance is faster than ADO.NET and OLEDB based on initial testing. And as always with performance testing, it is recommended to test in your own environment
ODBC is even faster than OLEDB on row by row inserts and DataDirect will enable you to completely replace the OleDb bulk insert functionality with DataDirect codeless bulk load using ODBC (keep reading).
2. ODBC Destination Batch Size meets DataDirect Bulk Load
When you create a new ODBC destination, you will see a property for batch size. This is the property that configures the SSIS 2012 use of parameter arrays to insert data; and DataDirect Bulk Load transparently converts this workflow into the database’s native bulk load protocol (similar to BCP, SQL Loader, etc) for drivers such as SQL Server, Oracle, Sybase, and Salesforce.com. This is one of the most exciting aspects of the 2012 release; and my initial tests with the DataDirect Connect for ODBC Oracle Wire Protocol driver bulk load capabilities were consistent with results observed by our performance lab.
We have seen codeless DataDirect Bulk Load improve load times from 45 minutes to just under 2 minutes.
To enable DataDirect bulk load, simply enable the “Enable Bulk Load” check box in your data source on the “Bulk” tab:
3. Breadth of options with 32-bit and 64-bit ODBC connectivity
You are covered with SSIS 2012 by DataDirect’s growing breadth of data connectivity options via 32-bit and 64-bit ODBC drivers on a complete range of Windows platforms and versions. This includes traditional data sources such as Oracle, Sybase, Informix, DB2 for I (iseries), DB2 for z/OS mainframe, DB2 LUW and DB2 Purescale; big data sources such as Hadoop Hive across Apache, Cloudera, MapR, Amazon EMR; and cloud sources such as Salesforce.com, Database.com, force.com, SQL Azure and NetSuite. From my initial testing, 32-bit drivers are required by the Visual Studio devenv.exe development environment; and 64-bit will be used for package execution. And the same 64-bit ODBC driver can be used with SQL Server 2012 Linked Server.
If you have a 32-bit ODBC data sources such as MS Access, MS Excel or Lotus Notes that require 64-bit access, we have DataDirect SequeLink to completely integrate all of your data with SSIS 2012.
Getting started with new ODBC source and destination components in SSIS 2012
2. Launch the Business Intelligence Development Studio (BIDS) and create new SSIS 2012 Integration Services Project
3. Create new DataFlow with ODBC source and/or destination
4. Inspect the batch size property in the ODBC destination. This value can be increased to further improve performance using DataDirect Bulk Load.
5. Run a basic work flow.
Check out my previous article that includes a section on how to configure the database for optimal bulk load performance.
Tell us your experiences with the new ODBC capabilities in SSIS 2012
Post a blog comment or call 1-800-876-3101 to speak with a live Solutions Consultant to learn more.