O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

1.1. What's New in SQL Server 2005 SSIS

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 developers. Microsoft included in the release new features like the Dynamic Properties task to help you dynamically alter the package at runtime. It also had extended logging and broke a transformation into many phases, called the multiphase data pump. Usability studies still showed that at this point developers had to create elaborate scripts to extend DTS to do what they wanted. For example, if you wanted DTS to conditionally load data based on the existence of a file, you would have to use the ActiveX Script task and VBScript to dynamically do this. The problem here was that most DBAs didn't have this type of scripting experience.

After five years, Microsoft released the much touted SQL Server 2005, where DTS is no longer an understated feature, but one of the main business intelligence (BI) foundations. It's been given so much importance now that it has its own service. DTS has also been renamed to SQL Server Integration Services (SSIS). So ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required