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 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
How to Convert Microsoft Word Format to HTML using Powershell
SQL Server 2012 SSIS upgrade considerations - SQL Server DBA
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |