Report of all connections in SSIS Packages

Quick script that will connect to a server and retrieve all the ssis packages from the msdb database and create a report of all the connections in said packages.  I shamelessly stole the xslt (and modified a bit) from here.  This will copy all the ssis packages on the server to your user profile directory under the ServerName directory you pass in.  Not thoroughly tested at all, use at your own risk.

param(
	[string]$serverName = 'ServerName'
)
cls
Import-Module SQLPS -DisableNameChecking

$outXMLPath = "$env:userprofile\$serverName"
$sb = New-Object system.Text.StringBuilder

$xsltString = @"
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:DTS="www.microsoft.com/SqlServer/Dts">
  <xsl:template match="/DTS:Executable" name="ssisconnections">
			<div class="PkgName">Package:  <xsl:value-of select="./DTS:Property[@DTS:Name='ObjectName']"/></div>
			<table>
				<tr>
					<th>Connection Name</th>
					<th>Connection String</th>
				</tr>
				<xsl:for-each select="./DTS:ConnectionManager">
					<tr>
						<td>
							<xsl:value-of select="./DTS:Property[@DTS:Name='ObjectName']"/>
						</td>
						<td>
							<xsl:value-of select="./DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name='ConnectionString']"/>
						</td>
					 </tr>
				</xsl:for-each>
			</table>
			<br/>
			<br/>
  </xsl:template>
</xsl:stylesheet>
"@

#for sql server 2005, for 2008 query sysssispackages & sysssispackagefolders
$pkgSQL = "
;WITH cte AS (
   SELECT cast(ISNULL(foldername,'MSDB') as varchar(max)) as folderpath, folderid
   FROM    msdb..sysdtspackagefolders90
   WHERE    (parentfolderid = '00000000-0000-0000-0000-000000000000') OR (parentfolderid IS NULL)
   UNION    ALL
   SELECT    cast(c.folderpath + '\' + ISNULL(f.foldername,'MSDB')  as varchar(max)), f.folderid
   FROM    msdb..sysdtspackagefolders90  f
   INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
)
SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
FROM    cte c
INNER    JOIN msdb..sysdtspackages90  p    ON    c.folderid = p.folderid
WHERE    c.folderpath NOT LIKE 'Data Collector%'"

$pkgSQL2008Plus = "
;WITH cte AS (
   SELECT cast(ISNULL(foldername,'MSDB') as varchar(max)) as folderpath, folderid
   FROM    msdb.[dbo].[sysssispackagefolders]
   WHERE    (parentfolderid = '00000000-0000-0000-0000-000000000000') OR (parentfolderid IS NULL)
   UNION    ALL
   SELECT    cast(c.folderpath + '\' + ISNULL(f.foldername,'MSDB')  as varchar(max)), f.folderid
   FROM    msdb.[dbo].[sysssispackagefolders]  f
   INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
)
SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
FROM    cte c
INNER    JOIN msdb.[dbo].[sysssispackages]  p    ON    c.folderid = p.folderid
WHERE    c.folderpath NOT LIKE 'Data Collector%'
"

$sb = $sb.append("
<html>
	<head>
		<style>
			body{font-family: verdana;}
			table{width: 98%; margin-left: 20px;}
			th{border: solid 1px black; background-color: aliceblue; text-align: left; font-size: smaller;}
			td{text-align: left; font-size: smaller;}
			.PkgName{border: solid 1px black; background-color: lightgoldenrodyellow; text-align: left; font-weight: bolder; padding: 4px;}
		</style>
	</head>
	<body>
")

#clear out the directory.  Obliterates everything.
if(Test-Path $outXMLPath){
	Remove-Item $outXMLPath -Recurse:$true -Force
}

try{
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $serverName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	if($srv.VersionMajor -lt 9){
		throw "This is only supported on sql server 2005 and greater."
		return;
	}
	
	if($srv.VersionMajor -eq 9){
		#package stored in msdb
		$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $serverName -Query $pkgSQL
	}
	else{
		#package stored in msdb
		$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $serverName -Query $pkgSQL2008Plus
	}
	
	#load dtsx xml into xml document so we can encode it to xml propertly.  
	#The xml is stored as an image type with &gt;, &tl; etc... in the database.
	$Packages | %{
		$xml = New-Object system.Xml.XmlDocument
		$xml.LoadXml($_.pkg);
		#in case a dtsx package is named the same in a different subdir
	    if(!(test-path -path "$outXMLPath\$($_.folderpath)")) {
	        mkdir "$outXMLPath\$($_.folderpath)" | Out-Null
	    }
		$xml.Save("$outXMLPath\$($_.folderpath)\$($_.name).xml")
	}

	#transform xml docs to html via xslt and store in user profile location
	$sr = New-Object system.IO.StringReader -ArgumentList $xsltString
	$xslt = new-object system.xml.xsl.xslcompiledtransform
	$xmlTextReader = New-Object system.Xml.XmlTextReader -ArgumentList $sr
	$xslt.Load($xmlTextReader);
	gci -Path $outXMLPath -Recurse -File | where{$_.Extension -eq ".xml"} | %{
		$sw = New-Object system.IO.StringWriter
		$xslt.Transform($_.FullName, $null, $sw)
		$sb = $sb.append($sw.ToString());
	}

	$sb = $sb.Append("</body></html>")

	Set-Content -Path "$outXMLPath\$serverName.html" -Value $sb.ToString()
}
catch{
	$_ | fl -Force
}




Find objects using linked servers via powershell

This script just executes a query on a per-database level to find objects via their definitions in the sys.all_sql_modules table that reference a linked server defined on the current server.  Right now it just spits the results out to a gridview.  Minimally tested, use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$outputs = @();

$servers = gc -Path 'C:\Servers.txt'

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $_
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	$srv.LinkedServers | %{
		$linkedServerName = $_.Name
		$srv.Databases | %{
			$linkedSQL = "
				SELECT 
					o.name AS ObjectName,
					o.type_desc,
					LEFT(m.definition, 100) AS ObjectDefinition
				FROM $($_.Name).sys.all_sql_modules m
				INNER JOIN $($_.Name).sys.objects o ON m.object_id = o.object_id
				WHERE m.object_id > 0 
				AND m.definition LIKE '%$linkedServerName%'
			"
			$dr = $srvConn.ExecuteReader($linkedSQL);
			
			while($dr.Read()){
				$output = New-Object -TypeName PSObject -Property @{
				    ServerName = $srvName
				    DatabaseName = $_.Name
					LinkedServerName = $linkedServerName
					ObjectName = $dr.GetString(0)
					ObjectType = $dr.GetString(1)
					Definition = $dr.GetString(2)
				}
				$outputs += $output
			}
			$dr.Close();
		}
	}
	
}
$outputs | SELECT ServerName, DatabaseName, LinkedServerName, ObjectName, ObjectType, Definition | Out-GridView

Fixing DBCC Check Error 2570

Whilst moving a database from a dying Sql Server 2000 box to a newer Sql Server 2008R2 server I ran a checkdb to ensure everything was kosher with the database (of course, after running DBCC UPDATEUSAGE).  Lo and behold, everything was not okay:

DBCC CHECKDB ([DATABASENAME] ) 
WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY 

Msg 2570, Level 16, State 3, Line 1

Page (1:131693), slot 60 in object ID 1365579903, index ID 1, partition ID 370969621233664, alloc unit ID 89494644523008 (type “In-row data”). Column “Example_DATE” value is out of range for data type “datetime”.  Update column to a legal value.

What does this mean exactly?  Well, it’s saying that (in my case), on page 131693 of file 1 a datetime value is not a valid value for the datetime type.  What is the invalid value?  Unfortunately, every time you try to do a select to see the value, you’ll get this same error.   So, we’ll have to run DBCC PAGE() in order to view the page data.  Start by turning on trace flag 3604 in order to print the results to the screen.

DBCC TRACEON (3604) 

Next, we’ll need to pass in both the file number and the page number to DBCC PAGE in order to see the contents.  This is contained within the original error message (screenshot below).  DBCC PAGE also takes a 3rd parameter for its print options, which is a number between 0 and 3.  Rather than describe them here, here is a link to the description.  In my scenario, I’ll be using print option 3, which is to show the page header plus the detailed per-row information.  I need this in order to be able to view the invalid data row.

DBCC PAGE ('DatabaseName', 1, 131693 , 3) 

image

The DBCC PAGE command will spit out the page information in text format.  I usually copy this out and paste it into Notepad++ (or whatever text editor you’re comfortable with) in order to make searching easier.  Here it is in the SSMS results pain:

Image(1)

So, now we’ve got a dump of the data, let’s look for the invalid row.  In notepad++, do a find on the column name that has the invalid data.  For example, in my case we’ll search on the CreateDate and look at the column values until we see something out of sorts:

image

Found it!  So, now I can scroll up to find the unique primary key value so we can update the column to  something not invalid:

image

Just to be sure, check to make sure that this record does indeed contain the invalid column:

image

Yup, sure does.  Now, as to what to the invalid date value for the column should be, who knows?  In my case, the dates of the other records around this invalid column were all only differing by milliseconds, and since we can assume that the CreateDate is just that, I doubt a difference of a few milliseconds is going to make a huge difference.  Make sure to check with whoever owns the data to make sure whatever value you need to replace is an acceptable value.

In my case, I just grabbed the next records’ CreateDate value and updated the invalid record to its value (the owners of the data had no clue what to set it to):

image

Okay, all fixed.  So, run DBCC CheckTable one more time to make sure there are no more errors:

image

Fixed.  Luckily, in my case it was just the one row.  Imagine having to do this to 1000 rows?  This is why you should be running your checks nightly if possible.