SQL Server 2008 Integration Services Connectivity Enhancements

One of the most important new features of SQL Server 2005 was Integration Services (SSIS), an Extract, Transform and Load (ETL) utility designed to replace the older Data Transformation Services (DTS) that shipped with SQL Server 2000. ETL utilities move data between heterogeneous platforms, optionally modifying, cleaning, standardising and formatting it along the way. For example, you can use SSIS to create a package that extracts data from an Oracle relational database that is used for online order processing, transforms it into an appropriate format, and then loads it into a SQL Server dimensional data warehouse that is used for data analysis. Although SSIS replaced DTS, it is not simply a tweaked or updated version of the older product. When designing SSIS, Microsoft went back to the drawing board in order to produce a data integration product that could genuinely compete in the enterprise arena, and it seems fair to say that they were successful in achieving this aim.

So how have Microsoft improved SSIS in SQL Server 2008? Well, as you would expect, there are many enhancements and new features and comprehensive coverage of these can be found at the SSIS site. It’s worth noting that significant progress has been made in terms of overall performance, as evidenced by the recent ETL world record reported by the SSIS team.
In this entry, however, I want to highlight the range and benefits of the SSIS connectivity options that are now available. Two of the measures by which ETL tools are judged are:

• The number of different data sources and destinations to which they can connect
• The amount of data they can process (extract, transform and load) in a given time

SSIS enables connections to data sources and destinations by using connectors, which you define by creating connection managers when creating your SSIS packages. SSIS 2008 offers an even wider range of connectors than SSIS 2005 so that you can now connect to sources and destinations including: SQL Server, Access, Excel, Oracle, Teradata, DB2, SAP BI, Siebel, Sybase, XML and flat files. That’s a pretty impressive range! Some of these connectors are built-in and some (for example those for Teradata, DB2, SAP BI and Siebel) are free add-ons that you can download from the Microsoft web site. Amongst these add-on connectors are the new high performance Microsoft connectors for Oracle and Teradata by Attunity, which offer optimal performance for loading data to and from Oracle and Teradata databases. These connectors were developed by Attunity in conjunction with Microsoft and, unlike other partner provided connectors, they are fully supported by Microsoft. Their performance advantage stems from the fact that the connectors access internal buffering APIs directly without going through a managed interface.

If you want more information on the range of connectivity options for SQL Server 2008 Integration Services, take a look at the white paper Connectivity Options for Microsoft SQL Server 2008 Integration Services on MSDN, which discusses the built-in and add-on connectors in greater detail.