I often use the SQL Server (MSDB) Database to store and execute SSIS packages. However, I found it cumbersome to migrate multiple packages back and forth from network locations to the MSDB or from the various environments (DEV, QA, PROD). So, I created a simple script to generate the dtutil commands necessary to quickly migrate the packages. It uses the system tables in the MSDB Database to generate the commands. I then simply copy and paste the output of the SELECT statement into a cmd prompt . This can be taken a step further and be put into a stored procedure and then loop through the dtutil commands using a xp_cmdshell wrapper to execute everything within SSMS. The script is nothing fancy, but I find it effective enough for most of my uses. The MSDN site for dtutil is a great resource that I often use to modify this script for the appropriate situation. Also, the “dtutil /?” command can be run in the command window to list all of different parameters for dtutil.
The below dtutil scripts provide two different outputs depending upon your needs. The first output is an example of copying from one SQL Server (MSDB) Database to another. The second output is an example of copying from SQL Server (MSDB) Database to a network location. These scripts were written for a situation where all three environments (DEV, QA, PROD) were on the same server, so I did not need to specify the destination server of the dtutil command. As a result, there is a parent folder/subfolder relationship that exists. Additionally, the SSIS packages are password protected, using the same password for each environment. NOTE: You may run into security issues when using some of the other security levels available.
Driver Temp Table Script: The below script builds the Temp Table – #PackageNames – for the Packages that are to be migrated. This temp table is the driver for the other scripts.
SELECT DISTINCT
parent_fld.foldername AS Parent_FolderName
,sub_fld.foldername AS Sub_FolderName
,pkg.name AS PackageName
INTO #PackageNames
FROM
msdb.dbo.sysssispackages pkg
,msdb.dbo.sysssispackagefolders sub_fld
WHERE
pkg.folderid = sub_fld.folderid
/* Folder that you want to COPY FROM; options DEV, QA, PROD */
AND sub_fld.foldername = ‘PROD’
;
Script 1 Details: COPY from PROD MSDB folder to QA MSDB folder. Using the Temp Table – #PackageNames – created in the above script. Generates the DTUTIL script to COPY from the Folder specified above (sub_fld.foldername = ‘PROD’) to the MSDB\QA directory in MSDB
SELECT
‘dtutil /SQL ‘ /* Specifies the location of an SSIS package. This option indicates that the package is stored in the SSIS Package Store (MSDB) database */
/* the below CASE statements build the command to identify where to COPY the package from */
+ CASE WHEN LEN(Parent_FolderName) = 0
THEN ”
ELSE Parent_FolderName + ‘\’
END
+ CASE WHEN LEN(Sub_FolderName) = 0
THEN ”
ELSE Sub_FolderName + ‘\’
END
+ PackageName
+ ‘ /COPY DTS;’ /* DTS: Specifies that the SSIS package is to be COPIED into the SSIS Package Store (MSDB) */
+ CASE WHEN LEN(Parent_FolderName) = 0
THEN ”
ELSE ‘MSDB\’ + Parent_FolderName + ‘\’
END
+ ‘QA\’ /* directory where the SSIS packages will be COPIED to; folder must already exist; options DEV, QA, PROD */
+ PackageName
+ ‘ /QUIET’ /* Do not prompt to overwrite the file if it exists */
FROM
#PackageNames
;
Script 2 Details: Generates the dtutil script to COPY from the Folder specified above (sub_fld.foldername = ‘PROD’) to the MSDB\QA directory in the SQL Server (MSDB) Database.
SELECT
‘dtutil /SQL ‘ /* Specifies the location of an SSIS package. This option indicates that the package is stored in the SQL Server (MSDB) Database */
/* the below CASE statements build the command to identify where to COPY the package from */
+ CASE WHEN LEN(Parent_FolderName) = 0
THEN ”
ELSE Parent_FolderName + ‘\’
END
+ CASE WHEN LEN(Sub_FolderName) = 0
THEN ”
ELSE Sub_FolderName + ‘\’
END
+ PackageName
+ ‘ /COPY File;’ /* File: Specifies that the SSIS package is to be COPIED to a file system location */
/* NOTE: double-quotes are required when the directory path has spaces, as in this example */
+ ‘”\\<directory location>\’ /* directory where the SSIS packages will be COPIED to; directory must already exist; options DEV, QA, PROD */
+ PackageName
+ ‘.dtsx” /QUIET’ /* Do not prompt to overwrite the file if it exists */
FROM
#PackageNames
;
Cleanup Temp Table: DROP TABLE #PackageNames ;
I hope you find this useful. Please let me know of other tips and tricks used with the dtutil command.
