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 >, &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
}
Sql Server Powershell 