John McCormack's blogs

SSIS and DTUTIL background

SQL Server Integration Services (SSIS) is a powerful tool, but migrating packages across SQL Servers can be a slow and thankless task if you don’t use automation. The single best way to do this is by using DTUTIL, a command-line utility provided directly by Microsoft. When it comes to moving 1000s of packages, I would only use DTUTIL because it can achieve in minutes what it would takes days to achieve using point and click.

–

Migrating a single package

Migrating individual packages from one SQL Server to another directly is also possible with DTUTIL. This is an example command below:

dtutil /SQL "SourceFolder/SourcePackage" /SOURCESERVER "SourceServer" /COPY SQL;"DestinationFolder/DestinationPackage" /DESTSERVER "DestinationServer"

• Specifies the source package location and server with /SQL “SourceFolder/SourcePackage” and /SOURCESERVER “SourceServer”.
• Copies the package to the destination SQL Server with /COPY SQL;”DestinationFolder/DestinationPackage”.
• Identifies the target SQL Server with /DESTSERVER “DestinationServer”.

–

Migrating multiple packages

This process can be scripted similarly for multiple package migrations. The easiest way is to generate the commands using T-SQL, print them to screen then copy and paste in to a command window.

If you run the command below on the source server, it will list out a command to migrate every package.

DECLARE 
	@sourceserver sysname = N'DBSERVER1'
	,@destserver sysname = N'DBSERVER2'
	,@foldername sysname = N'DATAWAREHOUSE'
	
SELECT 
'DTUTIL /SQL "'+sspf.foldername+'\'+ssp.[name]+'" /SOURCESERVER "'+@sourceserver+'" /COPY SQL;"'+sspf.foldername+'\'+ssp.[name]+'" /DESTSERVER "'+@destserver+'"' as cmd
FROM msdb.dbo.sysssispackages ssp
JOIN msdb.dbo.sysssispackagefolders sspf
ON ssp.folderid = sspf.folderid
-- WHERE sspf.foldername = @foldername -- Uncomment line to only use specific folder

–

Upload SSIS packages from disk

Uploading an SSIS package from your disk to SQL Server using DTUTIL is quite straightforward. The command line syntax you will use looks like this:

dtutil /FILE c:\mypackage.dtsx /DestServer DBSERVER2 /COPY SQL;"foldername/mypackage"

–

Download SSIS packages to disk (Great for backup)

Downloading an SSIS package to your disk from SQL Server using DTUTIL is also straightforward. The command line syntax you will use looks like this:

dtutil /SQL mypackage /COPY FILE; c:\destPackage.dtsx

Again, to download multiple packages, a script that lists out and provides a command would be the most efficient way to proceed. Try adapting the ‘migrate’ script above.

–

Summary

In conclusion, DTUTIL is a highly useful tool for managing SSIS package migration. While it might take a bit of practice to get comfortable with the syntax, its flexibility and efficiency make it worth the effort. Remember to always check your commands before executing them to prevent any unwanted actions. A full list of command is available from Microsoft Learn.

And that’s it, folks! I don’t have much other SSIS content, only this one which is more of a hack. With these simple and effective DTUTIL commands, you can now manage your SSIS packages with ease. Feel free to share this blog post with anyone who might find it helpful, and don’t hesitate to leave a comment below if you have any questions or want to share your own experiences with DTUTIL.