When SQL Server Integration Services (SSIS) released with Microsoft SQL Server 2005, it replaced an earlier set of tools, Data Transformation Services (DTS), that had been used for data migration since SQL Server 7.0 in 1998. SSIS is a much more robust feature, but for newer versions of Microsoft SQL Server, there is no backward compatibility for running existing DTS packages in SSIS. Although there is an option to migrate existing DTS packages to SSIS, the migrated package rarely successfully performs the function previously done in the DTS package. Much of the time, validation checks will fail, and tasks will not be functional. To reproduce the same data movements in newer versions of Microsoft SQL Server, it is much more reliable to manually rewrite the logic from a DTS package into a new SSIS package.
This article is not meant to be an all-encompassing guide but will cover the conversion of some necessary parts of DTS packages.
Before proceeding with replicating such data movement in SSIS, it is important to understand some of the key components of a DTS package and how they relate to and differ from those of an SSIS package, with which most users today are more familiar.
When you open a DTS package, you see the package workflow with icons connected by arrows. (See below for a sample DTS package with one Execute SQL task, two Transform Data tasks, and four connection objects. Explanations coming later.) This is similar to the workflow from the Control Flow tab of the SSIS designer. However, while in SSIS, all the icons are tasks, and all the arrows are precedent constraints, DTS is less intuitive.
Like a connection manager in SSIS, a connection object in DTS defines the parameters that enable a task to connect to a data store. While SSIS connection managers appear in a pane separate from the tasks, DTS connection objects appear within the tasks, but they are not tasks. (See below for what some connection objects look like. From left to right, these are connection objects for Microsoft OLE DB Provider for SQL Server, Microsoft Access, and Microsoft Excel 97-2000.) Opening the connections, you may find that multiple connection objects point to the same data store, and there may also be multiple tasks sharing the same connection object, resulting in numerous instances of the same connection object. The reason multiple connection objects are sometimes created that point to the same source is that tasks sharing the same connection object execute serially and can block each other. If parallel processing is desired, separate connection objects can yield better performance, and it is recommended to have multiple connection objects for databases used by multiple tasks.
The Execute SQL task in DTS contains the SQL code to execute and references the connection to the data store where the code is to be executed, just like the Execute SQL task in SSIS. An Execute SQL task may reference any connection object within the package, and one should never assume that an Execute SQL task is referencing a connection adjacent to it or positioned nearby. Make sure to identify the connection object with the exact same name as referenced in the task.
To determine where the data is moving, look for the Transform Data tasks. The Transform Data task in DTS is roughly the equivalent of a data flow task in SSIS, but there are a handful of differences. In the DTS package, the Transform Data task appears as a black arrow (see image below), and the task name is not visible until the task is opened (by double–clicking it) or the cursor hovers over it. Someone familiar with only SSIS may initially confuse the Transform Data task with the precedence arrows since it looks more like the precedence arrows than the corresponding Data Flow task in SSIS. The difference is that the precedence arrows are red, blue, or green, while the Transform Data task arrows are always black.
Once you double click the Transform Data task, a window opens that shows the task properties. This window serves a similar purpose as the Data Flow tab in SSIS. In DTS, the source and destination appear as tabs in the window, another difference from SSIS, which has separate icons for the source and destination. The Transformations tab is similar to the Mappings tab in SSIS Destination and shows the detail data flow at the column level.
With this information, one can document exactly what a DTS package is doing before attempting to recreate the logic in SSIS. This documentation will involve opening every task in the DTS package and identifying the work being done, the connections involved, and the data stores that each connection points to.
Unlike in DTS, there is no need to create multiple connection managers for the same data store in SSIS, since the connection manager maintains not one transaction but a pool of connections to a data store. If a new task needs to use a data store for which there is already an existing connection manager, reference that connection manager in the task. Although the package will still work with multiple connection managers for the same data store, the Connections Managers section will be unnecessarily cluttered.
The logic of a DTS package can be replicated in SSIS using the equivalent tasks, although some tweaks are often necessary to create a functional package.
The Data Flow task in SSIS is more sensitive to data type than the Transform Data task in DTS. The same data mapping that worked in a DTS package may generate warnings or errors when reproduced in SSIS. For example, when attempting to move data from a Unicode column to a non-Unicode column, or vice-versa, a data conversion would be required to get rid of the validation error.
In DTS, the same input column can be mapped to the multiple destination columns; this is not possible in SSIS, so in those scenarios, a Derived Column is needed to perform the correct mapping.
Although there is no reliable automated tool for converting DTS packages to SSIS, their similarities in functionality allows the logic from DTS to be easily replicated into SSIS.