Problem solve Get help with specific problems with your technologies, process and projects.

How to migrate DTS to SSIS and deploy SSIS packages

Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer tool.

I'm attempting the migration of DTS packages from SQL Server 2000 to SQL Server 2005. My problem is when I migrate the hundreds of existing DTS packages using the SQL Server 2005 Migration Wizard, they end up in the msdb.dbo.sysdtspackages90 table. I don't see them under Management/Legacy or anywhere else in the Management Studio.

Second, I've installed the DTS 2000 Designer from the Feature Pack. How, and from where do I access this tool?

First, only deployed SSIS packages are viewable from SQL Server 2005's Management Studio AND in order to see them you will need to connect to your instance of Integration Services NOT to the Database Engine. From the Object Explorer Choose Connect → Integration Services. What are deployed packages? They are packages that have been tested and validated and subsequently transferred to your Integration Services Instance where they can reside as either a file or in MSDB. These are your ready-for-prime-time packages.

Here's where we see a big difference between SQL Server 2000's DTS and SQL Server 2005's SSIS and a source of potential confusion. The DTS design tool is accessible through SQL Server 2000's Enterprise Manager and therefore can be considered part of SQL Server 2000. The SSIS Package Design Tool which is the Business Intelligence Development Studio (BIDS) has been decoupled from SQL Server 2005. You can have SSIS without installing the SQL Server Engine. During the development of SSIS packages they exist as .dtsx files within a BIDS SSIS Solution which may contain one or more SSIS Projects. These package files are totally separate from SQL Server 2005 and this is exactly what I mean by being decoupled.

From the Migration Wizard, I'd recommend that your destination migrated package be physical .dtsx files. This way you can open, inspect and test your newly converted packages using BIDS. Once you are happy with your SSIS package, you can deploy them to an Instance of SSIS by right-clicking on the SSIS project and choose Properties. From here, you will see the Build and Deployment Utility options in the tree view. You may also consider at this point to locking down and saving your BIDS solutions into source code control.

You can also deploy packages from with Management Studio by connecting to your Integration Services instance from Management Studio and drilling down into the File System and/or MSDB trees. Right-click on either File System or MSDB and choose Migrate. Keep in mind there that SSIS packages can be deployed to either a file system or to MSDB.

As for your last question, the DTS Designer tool allows you to open and modify your DTS packages from SQL Server 2005 Management Studio. From Management Studio, if you connect to a 2000 instance (because this is where your DTS packages reside) and choose Management → Legacy → Data Transformation Services, you will see a listing of DTS packages in that 2000 instance. From here, you can right click on a package and choose Open and you will see what looks exactly like the DTS Design environment under SQL Server 2000. That's it…that is how you access the DTS Designer. The Open choice simply won't be there unless you've installed the DTS Designer from the SQL Server 2005 Feature Pack.

Dig Deeper on Microsoft SQL Server 2005