Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Choose the Best Connectors for SSIS

By Mike Frost,

With the release of SQL Server 2005, Microsoft has targeted the ETL software segment by introducing SQL Server Integration Services (SSIS). While its roots can be traced back to the SQL Server Data Transformation Services (DTS) utility, SSIS is its own application, offering benefits such as integration with Microsoft's Visual Studio development environment, enterprise-level ETL functionality, and a greater focus on performance (through support of 64-bit architecture).

Despite these benefits, many users have been frustrated in their attempts to execute SSIS packages that connect to relational databases besides SQL Server. This is especially true when attempting to execute SSIS packages in 64-bit mode. SSIS-ready connectors for non-SQL Server relational databases do exist but few know what features to look for when choosing a solution that will guarantee the most SSIS functionality at the best possible performance. Here are some tips to consider when selecting a connector for use with SSIS packages to access relational databases such as Sybase, Oracle, and DB2.

32-bit vs. 64-bit

Most users choose to run SQL Server 2005 on 64-bit hardware to take advantage of the performance benefits of running SQL Server in 64-bit. For deployments of SQL Server 2005 on 64-bit hardware, executing SSIS packages in 64-bit mode is a natural preference because it allows for greater use of in-memory data processing, resulting in reduced disk I/O and improved package runtime execution performance. Bob Beauchemin, author of the SQL Server technical article Connectivity and SQL Server 2005 Integration Services, sums up the impact that this has on the choice of connector:

"SSIS can execute in 32-bit mode or 64-bit mode. Using 64-bit mode results in more available linear memory, which often translates into faster execution and data transformation. Thus, 64-bit support for any data source is beneficial."

In order to execute SSIS packages in 64-bit mode, all components, including the connectors, which are loaded by SSIS, must be true 64-bit binaries. Some connectors state support for 64-bit platforms but are actually only 32-bit components. At runtime, SSIS packages that use these components must be configured to use 32-bit mode thus restricting the performance of these SSIS packages. For this reason, it is always recommended to choose a connector for SSIS that supports 64-bit execution and does not simply support installation onto 64-bit platforms.

While support for 64-bit runtime execution may seem like an obvious feature to look for, support for 32-bit development may not. Despite the fact that SSIS packages can be deployed and run in 64-bit mode, use of Microsoft's Business Intelligence (BI) Development Studio requires 32-bit components. How this requirement affects the choice of connector is described by Bob Beauchemin:

"When developing in BI Development Studio, 32-bit data providers are always used for graphic-user interface operations such as providing metadata for drop-down list boxes and using the Query Designer. Therefore, if 64-bit providers will be used in production packages, the equivalent 32-bit provider must be available at design time."

So while the best choice for performance is a 64-bit connector, a 32-bit version of the connector must also be available in order to take advantage of the benefits of developing SSIS packages with the BI Development Studio.

Choosing the Right API

SSIS offers different API choices for connectors to relational databases other than SQL Server. Each comes with its own set of pros and cons which can help determine the best API for a particular situation.

API

Pros

Cons

OLE DB

  •   Most complete support with SSIS

  •   Very few 64-bit OLE DB providers available

ADO.NET

  •   Good support with SSIS

  •   No direct support for Data Flow Destination
  •  Somewhat limited availability of ADO.NET providers

ODBC

  •   Wide availability of ODBC drivers for possible data sources

  •   Requires the use of a bridge - introduces performance and reliability issues

Custom-API

  •   Designed to take advantage of performance of native utilities

  •   Limited availability for most databases
  •  Inconsistent support with SSIS - varies for each solution

As shown by this chart, 64-bit solutions that use OLE DB should always be considered and evaluated despite the fact that there are few 64-bit OLE DB providers available. This ensures that SSIS packages can be developed as robustly as possible and not limited in functionality.

Other Things to Look For

Other features to consider that will help you compare and evaluate various SSIS connectors include:

  • Clientless architecture - Connectors that do not require client libraries or bulk load utilities from the database vendor are significantly easier to install, configure, and deploy than client-based solutions. In addition, they typically offer the best performance for a wide range of usage scenarios including Source and Destination package types.
  • Ease-of-use - Features that make the package development process faster or easier are always desirable. Examples include features facilitating data source creation and modification, automated data type mapping, etc.
  • Quality / Reliability - Connectors should be capable of processing the work of multiple SSIS packages simultaneously without hanging or crashing SSIS. In addition, connectors should ensure data integrity through robust support for all SQL Server collations and other database character sets including Unicode.
  • Security - Support for features like Kerberos and SSL are particularly important for securing database access and encrypting data that passes over the network in security-sensitive environments.
  • Support for other SQL Server component environments - Organizations planning to use connectors with another SQL Server component such as 64-bit Linked Server should verify that the connector chosen will support it.

Conclusion

There is a lot to consider when choosing the right connector solution for SSIS but the choice can be made easier when looking at the benefits and drawbacks to the various options available. The table below summarizes what features to look for and their benefits.

Feature

Benefit

Matched set of 32-bit and 64-bit connectors

  • Enables 32-bit package development with BI Development Studio and 64-bit SSIS package runtime execution

OLE DB-based connectors

  • Exposes all possible SSIS package functionality when connecting to non-SQL Server databases

Clientless architecture that connects directly to database

  • SSIS packages configured and deployed faster and easier
  • Better performance achieved in a wide range of usage scenarios.

Ease-of use

  • Faster and easier development of SSIS packages

Quality / Reliability

  • SSIS packages run without failure
  • Data integrity ensured from start to finish regardless of the type of data being handled

Security

  • Secure access to the database from SSIS ensured
  • Sensitive data encrypted before passing over the network

Support for other SQL Server component environments

  • Enables support for SQL Server tools like 64-bit Linked Server

References

Bob Beauchemin, Connectivity and SQL Server 2005 Integration Services http://ssis.wik.is/File:Connectivity_White_Paper/Connectivity_and_SQL_Server_Integration_Services_forum_post.doc

Author Bio

Mike Frost is a product manager for DataDirect Technologies, the software industry leader in standards-based components for connecting applications to data, and an operating unit of Progress Software Corporation. In his role, Mike is involved in the strategic marketing efforts for the company's connectivity technologies. He has vast experience working with enterprise-data connectivity and is currently involved in the development of data connectivity components including ODBC, JDBC, ADO.NET, and XML.

Total article views: 8441 | Views in the last 30 days: 16
 
Related Articles
FORUM

Executing SSIS Package in 64-bit server

SSIS package failed as there's no 64-bit JET provider

FORUM

Executing SSIS Package in 64-bit server

SSIS package failed as there's no 64-bit JET provider

FORUM

SQL Server 2008 DTS Package Support

SQL Server 2008 DTS Package Support

FORUM

SSIS: Access to 64-bit SQL Server 2005

SSIS: Access to 64-bit SQL Server 2005

FORUM

Connectors in SSIS

Cannot view to edit connectors in my SSIS package

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones