Take Away

SSIS is an amazingly powerful new tool to work with importing and exporting data into your SQL Server databases or Analysis Services system, but it is not Microsoft's first attempt at that process. In previous versions of SQL Server, Microsoft included a product called Data Transformation Services, or DTS. If SQL Server 2005 is the only version you have used, you do not have to worry about "unlearning" anything or changing your paradigm.

But if you have used DTS, you probably have a few packages lying around that you need to either upgrade or migrate to SSIS. In this section, I explain a few of the best practices and other items that you need to know about moving to SSIS from DTS.

SQL Server Integration Services and DTS

When you upgrade a SQL Server 2000 system to SQL Server 2005, you will probably have DTS packages. SQL Server 2005 includes a Data Transformation Services "runtime" that allows many packages to operate. This layer is included to run many of the older DTS packages, but it does not allow you to design them.

I say "many" because some complex logic (such as the data mining task) is not supported in the runtime layer. In those cases, it is best to bite the bullet and redesign the package in SSIS without having to use the runtime incompatibilities. In the Appendix, I talk more about upgrading your system so that you can make the determination about how many of these you will have to redesign. Microsoft has done an admirable job in getting the system to work with a great deal of the DTS packages, but you will need to run the Upgrade Adviser to see what you have got to work with.

You can also run the DTS package as a task within the SSIS framework. You have the same limitations as when you use the runtime layer, but you can add functionality around the DTS package using this method.

You can migrate DTS packages to SQL Server 2005 using the Package Migration Wizard. This tool connects to a server and reads the packages it has. It then attempts to map the tasks it contains to SSIS. When it cannot map the task exactly, it picks something that it feels is the closest match. For this reason, it is important to review the process when it is complete.

The Package Migration Wizard only runs in the Standard Edition of SQL Server 2005 and higher.

There are few important points to know about this process even before you begin. The Migration Wizard is a copy of a DTS package. Whether you are bringing the package from another server or a SQL Server 2000 instance on the same server, the original package stays the same. The second thing is that some tasks do not map properly to SSIS, such as the following:

ActiveX script
Analysis Services
Custom tasks
Data-driven query
Dynamic properties
Execute packages tasks that load Meta Data Services
Parallel data pump
Transaction settings
UDL connections

Let's take a look at an example DTS package migration. I have SQL Server 2000 running on my system with a simple DTS package that performs a similar function to our earlier example. In this case, it exports the authors table from the pubs database to an Excel file. You can see that package in Figure 8-36.

Figure 8-36.

[View full size image]

In the SQL Server Management Studio, I have right-clicked the Data Transformation Services object in the Management, Legacy item in the Object Browser. That brings up a menu for the Migration Wizard, which you can see in Figure 8-37

Figure 8-37.

[View full size image]

After the welcome screen, I am asked to provide the connection for the DTS package, shown in Figure 8-38.

Figure 8-38.

[View full size image]

When I click the Next button, I am asked for the destination of the SSIS package. I enter my SQL Server 2005 instance name, and then click Next to continue. In this panel, I am asked to set which DTS packages I want to bring over. Because I only have one on this test system, I select it. You can see that selection in Figure 8-39.

Figure 8-39.

[View full size image]

On the next screen, I am asked to provide a logging location for this operation. I enter a file on my temp directory, and click Next to continue. At the last screen, I am asked to review the process and click Finish to finalize my choices. The process runs, and in this case it is successful.

Now I will open the Business Intelligence Development Studio to review the choices the Migration Wizard made, as shown in Figure 8-40.

Figure 8-40.

[View full size image]

It is important to remember that the DTS package is migrated as it stands into an SSIS package. One of the most common errors is not to realize that all the original connections are preserved. If you are exporting from a SQL Server 2000 system to an Excel file, the sources and destinations remain the same. I have seen many administrators sit in amazement that the data they just entered into their server did not make it to the destination, only to realize that the source system is still pointed at the original server.