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).
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 (Product Engineering, Office of the CTO). Below are the top 3 reasons I am excited about the 2012 release from my experience connecting enterprises from SSIS 2005 and 2008.
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).
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 ODBC Driver, 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:
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.
1. Download a trial of the DataDirect Connect for ODBC Wire Protocol drivers on Windows for 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.
Post a blog comment or call 1-800-876-3101 to speak with a live Solutions Consultant to learn more.
Get started with the new ODBC source and destination components in SSIS 2012 with a 15 day free trial of the DataDirect Connect for ODBC Wire Protocol drivers on Windows for SSIS 2012.
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.