Datasheet

Chapter 1: Welcome to SQL Server Integration Services

SQL Server SSIS Historical Overview

In SQL Server 7.0, Microsoft had a small team of developers work on a much understated feature of SQL

Server called Data Transformation Services (DTS). DTS was the backbone of the Import/Export Wizard,

and the DTS ’ s primary purpose was to transform data from almost any OLE DB – compliant Data Source

to another destination. It also had the ability to execute programs and run scripts, making workflow a

minor feature.

By the time that SQL Server 2000 was released, DTS had a strong following of DBAs and maybe a few

developers. Microsoft included in the release new features like the Dynamic Properties Task that enabled

you to alter the package dynamically at runtime. Even though DTS utilized extensive logging along with

simple and complex multiphase data pumps, usability studies still showed that developers had to create

elaborate scripts to extend DTS to get what they wanted done. A typical use case was enabling DTS to

load data conditionally based on the existence of a file. To accomplish this in DTS, you would have had

to use the ActiveX Script Task to code a solution using the file system object in VBScript. The problem

here was that DTS simply lacked some of the common components to support typical ETL processes.

Although it was powerful if you knew how to write scripting code, most DBAs just didn ’ t have this type

of scripting experience (or time).

After five years, Microsoft released the much touted SQL Server 2005, and SSIS, which is no longer an

understated feature like DTS. With the 2008 release, SSIS is now one of the main business intelligence

(BI) platform foundations. SSIS has moved so far up in importance that it now has its own service along

with the new name. This is entirely appropriate because so much has been added to SSIS. Microsoft

made a huge investment in usability, adding the first set of ETL tool - based components and upping the

ante again with this latest version. If what you need to do isn ’ t readily available, you now have the full

.NET library with VB and C# programming languages to add your own custom coding to message data

or manage the ETL process. However, you ’ ll be surprised how rarely you ’ ll need to drop into a coding

environment. In fact, as you dig into the toolset, you ’ ll find that things you may have needed to hand -

code in a Script Task are simply part of the out - of - the - box components.

What ’ s New in SSIS

SSIS is now in its second edition. If you are brand new to SSIS, everything will be new, but even if you

are already using SSIS each version just keeps getting better. This latest version of SSIS includes

enhancements for performance and scalability, upgrades to handle new TSQL capabilities, and the

addition of new components, including the long - awaited ability to use C# in the scripting tasks. We ’ ll hit

the highlights here.

c01.indd 2c01.indd 2 8/28/08 12:01:14 PM8/28/08 12:01:14 PM