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
go
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.