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:

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:

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.

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.

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.