Understanding How SSIS Is Different from DTS

If you used DTS in the past, it’s useful to look at how SSIS is different, because you might run into a backward compatibility question on the exam. In this section, we’ll cover some of the improvements made in SSIS as well as how DTS functions in the SQL Server 2005 environment.

Here’s how SSIS is different from DTS:

• SSIS now runs as a service and in fact doesn’t even require SQL Server 2005 to be installed to function (but you still need the license!).

• SSIS was completely rewritten to use .NET objects and concepts. The most visible place you’ll notice this is when you write scripts; you’ll use the Visual Studio 2005 editor with IntelliSense instead of the old loosely typed VBScript, and you should find yourself writing a lot less scripting code because of the richer set of tasks and transforms.

• SSIS packages are saved in an XML format (.dtsx files) and compiled into .NET binaries; by contrast, DTS either stored them in a private format or gave you the option to save them as Visual Basic 6–compatible code.

• Maintenance plans are now SSIS packages, with new tasks for standard items such as backup and index rebuilds.

• SSIS packages contain only the most recent version of the code, whereas DTS packages actually included all the versions created.

• Enterprise Manager was used to both design and administer DTS. Now you use SQL Server Management Studio for administering SSIS, and you use Business Intelligence Development Studio (BIDS) or Visual Studio for creating and maintaining SSIS packages. Working in Visual Studio means you’ll be able to insert breakpoints and debug packages; you also can make references to other Visual Studio projects/.NET assemblies (for example, libraries of functions, and so on) and use them in your project.

• SSIS can execute DTS packages, which is a great way to leverage existing work inside the new tool set.

• SSIS also comes with new tools for logging and package configuration.

Versioning Has Changed

In SQL Server 2000, DTS packages stored multiple versions of the package; you could choose which one to execute by specifying the version ID. More than once someone would make a change to a package and wonder why the old behavior was still occurring-it was because the version ID was being specified in the job! Although it was not the worst behavior, it was confusing at times and made packages bulky if they were frequently modified.

SSIS changes the model to a simpler one. Each package is an XML document that can be checked into the source control tool of your choice. If you want to deploy an older version, pull it from source control and deploy where needed. A nice benefit of this change is that you can easily see the difference between two versions by just doing a simple diff.

In terms of backward compatibility, you actually end up in pretty good shape. DTS packages will continue to work without modification, either by running directly or by calling from within an SSIS package, as mentioned previously. Editing DTS packages does have a caveat, though; if you want to make changes to DTS packages deployed to a SQL Server 2005 server, either you need a SQL Server 2000 instance installed on that machine or you have to install the DTS Designer, which is a free download from Microsoft.