View DTS packages with Powershell – DTS to SSIS planning

22 August,2013 by Tom Collins

Question: How can I gather DTS package counts from a large number of SQL Server Instances, (over 500) ? We’re in the planning stage of a SQL Server 2012 upgrade . DTS is discontinued in SQL Server 2012, and I’m sizing the amount of effort required  for upgrading DTS to SSIS.

Answer:  Estimating the amount of effort of DTS to SSIS is critical SQL Server 2012 SSIS upgrade consideration. Your immediate requirement is to document the package count but further effort will be required by developers  to analyse the packages .

Using Powershell , it is easy to iterate through a number of SQL Server Instances and gather the DTS package count

The packages are generally stored  in MSDB database in the table "sysdtspackages". The “packagedata” column  stores the DTS contents , but you’re requirement is to see the names and ids. This information allows you to count the packages.

This query returns the count based on distinct package names


use msdb
select count(distinct(name)) from dbo.sysdtspackages


Wrap this statement in a Powershell script – and let the script do the work for you.

The Powershell script iterates through a list of SQL Server Instances and executes the query on msdb.dbo.sysdtspackages. The results are stored on DataTable. The DataTable is piped out and output is placed in a HTML file using the ConvertTo-html  Powershell cmdlet.


  $isodate=Get-Date -format s 
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="dts_packages_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile

 $dt = new-object "System.Data.DataTable"

 foreach ($svr in get-content "C:\Instances.txt"){
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $sql = $cn.CreateCommand()
    $sql.CommandText = "select @@servername,count(distinct(name)) from dbo.sysdtspackages"
    $rdr = $sql.ExecuteReader()

$dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server 2012 DTS packages " | Set-Content $outputfilefull 

 Read More

How to Convert Microsoft Word Format to HTML using Powershell

SQL Server 2012 SSIS upgrade considerations - SQL Server DBA

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on View DTS packages with Powershell – DTS to SSIS planning | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer