Day: March 19, 2014

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
}