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:

 

 

Advertisements

Author:

I'm a Business Intelligence Developer working for a financial services company. My focus is on the Microsoft suite of BI tools.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s