ETL Tools & Microsoft SQL Server Integration Services: The What, Why & How

Business Intelligence (BI) systems are now a core part of any corporate IT department, providing the business with key information, improving decision making and supporting day-to-day operations. Underpinning such applications are information systems and databases organized into the well-defined structures that the BI applications need in order to run. These underlying database structures are typically and collectively known as data warehouse (DW) systems (although from a purist’s perspective only a subset probably is).

There are many reasons that data is stored in these separate data warehouses, such as:

• • Performance – Having a separate dedicated database structured to better support known database queries typically means better performance of those queries. For example it is common practice to create aggregates and summaries of data within the data warehouse structure itself

• • Impact on Transactional Systems – By separating BI requirements and decisions support away from the business’s operational/transactional systems means those systems are isolated from any resource demands required by the BI systems

• • Collection of Historical Data – Transaction systems typically just have current data. By storing ‘snapshots’ of this data collectively over a period of time allows historical data to be built

What all data warehouse projects have in common however is the challenge of getting data from the source (transactional) systems of the business into the structures of the data warehouse. Depending on the specific environment, as well as the complexity and number of source systems this process, known as Extract, Transform and Load (ETL), can be time-consuming, costly and very difficult. As a result an entire market has grown around the software companies that build and supply the tools to attempt to make the process easier (the ETL tools market).

ETL tools have now become a critical component in overall integration architectures, especially for business intelligence and data warehouse projects. The last few years have seen a number of software vendors emerge and grow on the back of the ETL market, typically with software license price tags running to the tens of thousands and often hundreds of thousands of dollars.

However, when Microsoft announced SQL Server 2005, they introduced a free new product component as part of the server suite, called SQL Server Integration Services (or SSIS for short). This announcement heralded Microsoft’s first serious entry into the ETL market. It was a bold statement of their intentions, and overnight changed the dynamics of the previously unchallenged ETL vendors. As a new Microsoft SQL Server Business Intelligence application, and the successor to Microsoft’s previous Data Transformation Services product, it has become a platform for a new generation of high-performance data integration solutions.

Now with on its second major release, in conjunction with SQL Server 2008, SSIS is a serious con- tender in any ETL solution and data warehouse project design, especially when the (free) cost is brought into consideration.