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.

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

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

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

$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