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.
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)
[IMAGE]
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, ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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:
[IMAGE]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.
Check out his blog:
SQL Server with Mr. Denny
