Finding Execute DTS 2000 Package Tasks Prior to Upgrading to SSIS Packages

By: Brian P ODwyer   |   Updated: 2014-03-31   |   Comments   |   Related: > Integration Services Upgrade

Problem

We have to migrate about 500 DTS packages from SQL 2000 to SQL 2012 SSIS packages knowing that we will have to first migrate to 2008R2 SSIS packages and then to SSIS 2012 package code. We expect that there will probably be conversion issues, but we really hope that there will not be too many issues. However, one of the known issues with converting to SSIS 2012 packages is with the Exec80PackageTask or Execute DTS 2000 Package Task. This task is deprecated and unavailable in SSIS 2012 which means we cannot use backwards compatibility to run SSIS packages containing them. We have to remove or replace these tasks with Execute Package Tasks either in 2008 or 2012 versions.

While knowing that the Exec80PackageTask is deprecated we were still left with the daunting task of finding them in the 495 DTS packages, all of which were written over the years by staff in a different area and were undocumented when we got to working with the staff. In addition to the problem with Exec80PackageTask itself there is the complexity of two types of tasks, one where it is calling an external DTS package either in SQL or the file system and the second type where the code is embedded in the task itself and requires you to open it in DTS 2000 editor to see the code.

The issues we are facing are we have to migrate the DTS packages to SSIS 2008R2 packages and then either fix the packages in BIDS 2008R2 and upgrade to SSIS 2012 or upgrade the migrated packages to SSIS 2012 and fix the issues in 2012 SSDT. It will require us to install the Backwards Compatibility Tools (tip 2165) in order to use the DTS 2000 editor in BIDS to look into the Exec80PackageTask and find either the package called or embedded code. The Migration Wizard (tip 1224) will put the DTSX files in the location we specify which should be the file structure for a BIDS solution. With the BIDS solution we can later upgrade the DTSX packages into a 2012 SSDT SSIS solution and replace the Exec80PackageTask with an Execute Package Task and then save the upgraded DTSX making any other changes as needed finally giving us SSIS 2012 packages we can run.

You might ask why do we need to migrate and why not fix them in DTS first. You could, but the fact is that we did not want to interrupt our only production server and by having the migrated DTSX files, we can use PowerShell to search the XML, which is just text for Exec80PackageTask tasks in the DTSX packages. The ease of searching through the nearly 500 packages versus touching each one allows us to target known problem packages up front. We may still have issues, but we can at least find those with Exec80PackageTask tasks.

Solution

My solution is simple.  PowerShell scripts that search through all the DTSX files in the BIDS solution folder looking for strings and then outputting the file name each time they find that string in a file. I have two scripts that search either for task that calls another DTS package, located in MSDB (Location="SqlServer"), but could be File System (Location="StorageFile") or the embedded code task (Location="EmbeddedInTask"). You will get output to the PowerShell console that tells which DTSX files have issues and how many issues are in each DTSX package. A simple and effective method for finding and fixing these types of issues. I know that the migration log will also have a record for these tasks, but there are two per task with no differentiation between types and in the case of a large migration you may have to, or want to, split it up into smaller chunks. You may run into memory issues when converting a large number of DTS packages to DTSX packages or have crashes in either BIDS or from SSMS when using the Package Migration Wizard.

There are several methods to migrate with the Migration Wizard from BIDS as Jeremy Kadlec discusses in tip 1224 or from SSMS using the Migration Wizard found at Management > Legacy > Data Transformation Services. Just right click on the Data Transformation Services node and choose Migration Wizard. Jeremy's method will allow you to create a BIDS solution with the DTSX packages in the solution just be sure to use the Solution/Project directory as the destination for migrated packages. With SSMS you can save to SQL Server in which case it will go into MSDB in dbo.sysssispackages (SQL 2008 and up) on the destination server or you can choose the file system for migrated DTSX packages. If you do save to the file system with SSMS then you will need to import the packages into BIDS. If you saved to MSDB then you can use an adaption of the code found here to create DTUTIL commands to export from MSDB. In either method you still have to install Backwards Compatibility support for both BIDS and SSMS.

Create the DTUTIL code for exporting DTSX from MSDB

We can use T-SQL to create DTUTIL commands for exporting DTSX packages from MSDB in SQL 2008 and higher, SQL 2005 has dbo.sysdtspackages90 and dbo.sysdtspackagefolders90 for the packages tables. While this is used for creating DTUTIL export commands; we could create import commands the same way or use Excel to create them from a listing of DTSX package names.

 SELECT
CAST( 'DTUTIL /SQL  "'
  + CASE 
   WHEN LEN(fld.foldername) = 0 THEN ''
   ELSE fld.foldername + '\' 
   END 
  + pkg.[name]
  + '"  /COPY FILE;"D:\CCHP\TEMP\' 
  + CASE
   WHEN LEN(fld.foldername) = 0 THEN '' -- this is if the DTSX files are in root and not subfolders
   ELSE fld.foldername + '\'  -- this gives the subfolder path in sysssispackagefolders
   END
  + pkg.[name] 
  + '.DTSX" ' 
  + ' /QUIET' -- suppresses prompt to delete older file with same name that exists
  AS NVARCHAR(4000)) AS dtutil_command
FROM
 msdb.dbo.sysssispackages AS pkg
  INNER JOIN msdb.dbo.sysssispackagefolders AS fld
   ON pkg.folderid = fld.folderid
WHERE 
 LEN(fld.foldername) = 0  -- comment out to choose folder below 
-- fld.foldername = 'Myfolder' -- you can adjust this to required folder
ORDER BY
   fld.foldername
 , [name]

The output would be in SSMS with each row similar to:

If you prefer to use Excel for the command creation you could create Import commands to either MSDB in the Myfolder folder:

Or to the File System in the \Program Files\Microsoft SQL Server\100\DTS\Packages\Myfolder directory on a server with Integration Services installed:

In the above formulas the DTSX package name was in column B of the worksheet you are on, B2 being the formula for that row only. The DTUTIL commands created if needed for export from MSDB we can then look at the PowerShell scripts to search the DTSX files.

PowerShell script to search for Exec80PackageTask tasks

The PowerShell I will be using is based on Version 2 and higher and does not use any special add-ins or Providers, just native functionality. It is the Select-String commandlet which searches for a string in files. Nothing fancy, but there are some interesting things you can do with it. I use it to search a binary image file for a string in a wrapper for the image. The commandlet works great to find Strings in any files with text strings in them.

The output is to the Powershell console, but you could put it into a variable and then write to a file. The key part is that there will be a row for each Exec80PackageTask found and it will have the DTSX file name at the end so we can count the number of issues and see where they are. The image below shows a representative console output window.

Next Steps

We can edit the affected DTSX files and replace the Execute DTS 2000 Package Task with the new Execute Package Task or refactor it to remove the need for that task. We can also modify the PowerShell to find one more deprecated task, the ActiveXScriptTask. We would just search for ActiveXScriptTask.ActiveXScriptTask in the pattern.

As a final note be sure and understand the difference between File System, SSIS specific hard coded location \Program Files\Microsoft SQL Server\100\DTS\Packages\ and file system, a location on the disk file structure you choose. The DTUTIL utility recognizes File System and File in the syntax as just mentioned.

• To learn more about SQL 2000 DTS Conversion issues look here: SQL Server 2000 to SQL Server 2008 DTS Package Conversion Runtime Issue
• To see a step by step migration process: Upgrade SQL Server DTS Packages to Integration Services Packages

About the author

Brian P ODwyer is a BI Analyst at Cook Children's Health Care System who knows both Infrastructure and SQL server

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips