Copy SSIS Packages between SQL Server Instances

I’m in the process of setting up a mirrored server and I’m looking to make fail-over as painless as possible.

SSIS Packages are used quite extensively in our environment so it would be useful to mirror these. I’ve written a little powershell script to get this done. It’s fairly limited, will only copy packages created in the SSIS designer in the root folder, as that’s all I needed.

Tested, but not battle ready, on SQL Server 2008 R2. Use with care for now. Just change the source and destination server variables, and possibly the path to dtutil.exe, and you should be all set.

Add-PSSnapin SqlServerCmdletSnapin100;
Add-PSSnapin SqlServerProviderSnapin100;

$sourceServer="SQLInstance1";
$destinationServer="SQLInstance2";

try
{
	# Will only import packages created in the SSIS Designer in the root folder. 
	# For details; http://technet.microsoft.com/en-us/library/ms181582.aspx
	$ssisPackageQuery="SELECT  [name] FROM sysssispackages WHERE packagetype = 5 AND folderid = '00000000-0000-0000-0000-000000000000'";

	$packages = Invoke-SqlCmd -ServerInstance $sourceServer -Database "msdb" -Query $ssisPackageQuery;

	foreach($pkg in $packages)
	{
		$packageName = $pkg.name;
		$exec="`"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe`"";
		$importParams = "/QUIET /COPY SQL;`"$packageName`" /SQL `"$packageName`" /SourceServer `"$sourceServer`" /DestServer `"$destinationServer`"";
		$exit = Start-Process -FilePath $exec -ArgumentList $importParams -NoNewWindow -Wait -PassThru;
		$exitCode = $exit.ExitCode;
		if($exitCode -ne 0)
		{
			# log error
			Write-EventLog -LogName "Application" -Source MSSQLSERVER -EventId 3001 -EntryType "Error" -Message "CopySSISPackages.ps1: Failed with params $importParams";
		}
		else
		{
			# log success
			Write-EventLog -LogName "Application" -Source MSSQLSERVER -EventId 3001 -EntryType "Information" -Message "CopySSISPackages.ps1: Succeeded with params $importParams";
		}
	}
}
catch [System.Exception]
{
	$msg = $_.Exception.Message;
	Write-EventLog -LogName "Application" -Source MSSQLSERVER -EventId 0 -EntryType "Error" -Message "CopySSISPackages.ps1: $msg";
}

Leave a Reply