I recently had to migrate a server with over twenty packages, and I knew I didn't want to click-click-click over and over again. 🙂
I looked around and was reminded of dtutil, the utility designed to manage DTS and then SSIS packages from the command line. I found a comment at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx that included a SELECT statement to generate dtutil commands based on the contents of msdb.dbo.sysssispackages:
select 'DTUTIL /SQL "'+f.foldername+'"/"'+ name +'" /DestServer [YOURSQLSERVERDEST] /COPY SQL;"'+f.foldername+'"/"'+name+'" /QUIET'
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
I played with it a little and it did serve my purpose - I was able to generate twenty dtutil commands, drop them in a Notepad batch file, and successfully run that batch from Windows to move the packages.
I fiddled with the script and started testing it on different SQL Server versions. The biggest gotcha I found was that on SQL Server 2005 there is no ssispackages table - the comparable table is sysdtspackages90 (and sysdtspackages90folders). A quick modification to the script to add a version check dealt with this:
-------
/*
SSIS Package Copy with DTUTIL in xp_cmdshell
Run on source server where packages are stored
Set parameter @TargetServer to server name where packages are moving
Modified be Andy Galbraith @DBA_Andy from an idea at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx
Tested on MSSQL 2005/2008/2008R2/2012/2014
*/
SET NOCOUNT ON
DECLARE @TargetServer sysname, @SQLVersion char(4)
SET @TargetServer = 'ServerB'
SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)
/* PRINT @SQLVersion */
IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
select 'EXEC xp_cmdshell ''DTUTIL /SQL "'+f.foldername+'\'+ name
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET'''
from msdb.dbo.sysdtspackages90 p
inner join msdb.dbo.sysdtspackagefolders90 f
on p.folderid = f.folderid
END
ELSE /* 2008+ */
BEGIN
select 'EXEC xp_cmdshell ''DTUTIL /SQL "'+f.foldername+'\'+ name
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET'''
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
END
END
-------
In the above script I wrapped the dtutil statements in xp_cmdshell calls so that I could run it from SQL Server rather than the Windows command line (or batch files).
If your environment doesn't support xp_cmdshell (which is a completely different best practices discussion - see a great post by K Brian Kelley (blog/@kbriankelley) here about the risks of enabling xp_cmdshell in your environment) then it is easy to remove the xp_cmdshell piece to return the results back to simple dtutil calls:
-------
/*
SSIS Package Copy with DTUTIL
Run on source server where packages are stored
Set parameter @TargetServer to server name where packages are moving
Modified be Andy Galbraith @DBA_Andy from an idea at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx
Tested on MSSQL 2005/2008/2008R2/2012/2014
*/
SET NOCOUNT ON
DECLARE @TargetServer sysname, @SQLVersion char(4)
SET @TargetServer = 'ServerB'
SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)
/* PRINT @SQLVersion */
IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
select 'DTUTIL /SQL "'+f.foldername+'\'+ name
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET'
from msdb.dbo.sysdtspackages90 p
inner join msdb.dbo.sysdtspackagefolders90 f
on p.folderid = f.folderid
END
ELSE /* 2008+ */
BEGIN
select 'DTUTIL /SQL "'+f.foldername+'\'+ name
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET'
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
END
END
-------
