Posted in SSIS

Copying SQL Stored SSIS Packages across instances

The problem

 

At the organization that I work at we run all our SSIS Packages from SQL Server (as opposed to the file system).

 

We had a need to copy all our packages from one server to another (From Production to our Disaster Recovery server). If you’ve ever tried this with Management Studio you’ll know that you painstakingly need to copy them one by one.

 

What packages are on my server?

 

The following script can be used to query which packages are on the server:

 

select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName

 

DTUtil

 

The Dtutil command line utility can be used to manage packages.

It can be used to delete or copy the packages that you’re interested in.

You can find all the parameter options at Books Online (http://msdn.microsoft.com/en-us/library/ms162820%28v=SQL.100%29.aspx)

 

Here are the options and arguments that suited me in copying one package:

dtutil /SQL “SourceFolder\SourcePackage” /SOURCES “MyProdServer” /COPY SQL;”TargetFolder\TargetPackage” /DESTS “MyDRPServer” /Quiet

Now that we’ve got the list of packages, and the DTUtil syntax for what we’re trying to do, we can generate our DTUtil commands and run them on the command line.

 

select ‘dtutil /SQL “‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /SOURCES “ProdServer” /COPY SQL;”‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /DESTS “DrpServer”‘
+ ‘ /Quiet’
from (
select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName
) pk

 

I’ve run this on my local instance, here’s the output: