SQLServerCentral Article

How to Choose the Best Connectors for SSIS

,

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating