Data Transformation Services vs. SSIS: The key differences
Denny Cherry, Contributor
Migrating from Data Transformation Services (DTS) to SQL Server Integration Services (SSIS) is a
major upgrade. The two engines are very different and developers may find the move daunting as many
tasks, including the ActiveX component, require a complete rewrite.
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in October 2009
In SQL Server 7.0 and SQL Server 2000, DTS was part of the
database platform, and it was edited with the Enterprise Manager tool. SSIS, on the other hand, was
introduced in SQL Server 2005, and is a separate Windows service. SSIS packages are edited with the
Business Intelligence Development Studio (BIDS) tool. In addition, while a 32-bit environment is
the only option with DTS, 64-bit data processing is available in Integration Services.
Furthermore, SSIS can run VB.NET or C# code directly in the SSIS package as a script object,
which are not just VBA scripts but fully precompiled .NET applications that can reference native or
third-party .NET libraries, COMM objects, etc.
Process constraints
When a Data Transformation Services package is created, each object runs in its own right and
you draw precedence constraints between the objects.
The concept remains the same in SQL Server Integration Services, but objects can now also be
grouped together in a sequence container. This allows you to run an object, then kick off the
sequence container and run the object (or objects) in the container.
The objects in the container can have constraints between them as shown in Figure 1.
Figure 1: Example of constraints in a sequence container (Click to enlarge)
The following five constraints are shown above:
- Green arrows without a small blue box indicate the prior object must complete
successfully.
- Blue lines mean the prior object must complete.
- Small blue box indicates an expression must match successfully.
- Solid color arrows in an expression indicate a logical AND is in place.
- Dotted arrow indicate a logical OR is in place.
(Red lines, which are not shown, indicate a prior object must fail to complete
successfully.)
In the case of the arrow between the Clear Working Folder file system task and the Process
Updates execute SQL task, the Clear Working Folder object must be successful and the expression
within the object must also evaluate successfully.
In the case of the constraint line between the Process Updates SQL task and the Process New
Domains data flow task, either the Process Updates object must be successful or the expression in
the constraint must be true.
Task objects
A total of 17 task objects are available in DTS packages for SQL Server 2000. Of the 17 tasks,
five are for transferring database objects from one server to another. This leaves only 12 tasks
for working with data or running other code or commands.
In comparison, SSIS includes 29 tasks, which doesn't even count all the data flow objects used
for moving data from one location to another, and is in addition to 11 database maintenance tasks.
Of the 29 tasks, six are for transferring database objects, which leaves 22 tasks for running
commands or scripts.
Data flow transformations
Data Transformation Services basically has only a single data transformation option: columns can
be mapped to other column names.
SSIS has 29 different data transformation options, including the ability to split values, handle
lookups, merge transformations, do percentages based on samplings of data, do row sampling, run
.NET scripts against the rows while in flight, and sort the data. This provides a wider range of
options when moving data between servers.
(Note: Since there are so many options, be careful when selecting one to ensure you don't pick
the wrong transformation.)
"Gotchas" in SSIS
Many first time SSIS developers are tripped up by the fact that unlike DTS, SSIS does not
automatically handle ASCII to Unicode conversions seamlessly. Instead, a data conversion task is
needed to move from ASCII to Unicode.
Another item SSIS developers will miss from DTS is the dynamic properties task, which allows
almost any object property to be dynamically set based on vales from variables, queried from a
database, or loaded from a config file. While to some degree, this concept is still possible with
the script tasks in SQL Server Integration Services (for example the .NET script task can change
the properties of a connection object, or change the values of SSIS variables), you don't have the
same amount of freedom because most of the SSIS task is compiled before it is run. Therefore, the
number of things that can be dynamically changed is much lower than with Data Transformation
Services.
While there are many enhancements to SSIS, some DTS features were lost and this initially turned
some developers off. Once the migration process is complete, however, you will find SSIS is a
powerful and flexible tool that can handle all your data transformation needs.
ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including
MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of
expertise include system architecture, performance tuning, replication and troubleshooting. He
currently holds several Microsoft certifications related to SQL Server and is a Microsoft
MVP.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation