Skip to content

Posts from the ‘SSIS’ Category

19
Mar

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
}




24
Jan

Add a remote server to your local SSIS instance

In order to add a remote server to your local instance of ssis you have to add said instance to your config file for ssis.  The location of your config file for ssis is C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml  (if on x64). 

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.\DENALI</ServerName>
    </Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>WhateverServerYouWantToConnectTo</Name>
      <ServerName>ServerName\InstanceName</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

After you’ve added the server, you’ll need to restart the Integration Services service on your local machine.  Then, open up the integration services on your local machine, expand the Stored Packages node and you should see the machine that you just added.

SSIS

26
Oct

Moving Large Amounts of Data

We have this database which is about 300 gig.  A substantial portion of this data is logging data, and a substantial portion of the logging data is simply heartbeat records.  Why on earth the home page logs to the database is beyond me, but moving on…

There are roughly 280 million records in one table in particular that need to be moved to an archiving database on the same server.  Usually, my approach is to BCP out the data that I want to keep and BULK INSERT it back into a new table, but I decided to try a new approach based upon Henk Vandervalk’s article here.

This approach uses multiple streams in parallel in SSIS to SELECT the data out into the new table.  This is accomplished by using the MODULO operator against an IDENTITY field in the table to partition out the streams based upon the remainder of the IDENTITY field.  Here’s an example:

CREATE TABLE dbo.TestModulo(
	ID		INT IDENTITY(1,1),
	VAL		VARCHAR(10)
)
GO

INSERT INTO dbo.TestModulo(Val)
SELECT 'Test'
GO 100

SELECT TOP 5 * FROM dbo.TestModulo
WHERE (ID%4)=3
OPTION (MAXDOP 1)

SELECT TOP 5 * FROM dbo.TestModulo
WHERE (ID%4)=2
OPTION (MAXDOP 1)

SELECT TOP 5 * FROM dbo.TestModulo
WHERE (ID%4)=1
OPTION (MAXDOP 1)

SELECT TOP 5 * FROM dbo.TestModulo
WHERE (ID%4)=0
OPTION (MAXDOP 1)

As you can see in the image below, this partitions the table up into sets based upon the remainder of the ID field divided by the MODULO operator rounded upwards. 

image

So, in SSIS I created a Data Flow Task.  In said Data Flow Task I created 4 OLEDB Sources.  In the sources is where I put the Sql Select statements that utilize the MODULO operation to divide up the sets of data.  I then dragged the output of all 4 to a UNION ALL transformation and then the output of the union all goes to the Sql Server Destination.  Of note, you can only use the Sql Server Destination if the destination table is on the local server.  If not, you must use an OLEDB Destination.

image

The result of this was moving 227 million rows in about an hour.  Not bad!  When doing this via BCP/BULK INSERT, the BCP out alone took over an hour, and the import took another 45 minutes on top of that.  This essentially cut down the loading time by about half!

Another thing I did to speed up the transformation was to increase the default packet size on the connections from 4k (Sql Server default) to 32767 (also mentioned in Henk Vandervalk’s excellent post).