SSIS packages can be saved in three types of storage locations: SQL Server, File System, and SSIS Package Store. When an SSIS package is saved into SQL Server, it is saved in the msdb database, specifically, the dbo.sysdtspackages90 table in msdb. There are many advantages of saving SSIS packages in SQL Server vs. in FileSystem or SSIS package Store (which is a special file system location in Program Files\Microsoft SQL Server\90\DTS\Packages). When packages are saved in SQL Server, they can be backed up with the normal msdb database backup process. The access to the packages can be controlled by the security mechanism built into SQL Server through database roles. The packages are also easy to query using T-SQL. In one word, SQL Server is the better storage option for production environments.

However, packages stored in SQL Server are not as portable as the ones stored in files. To copy a package stored in a file, you can simply copy the file from one machine to another. To copy a package stored in SQL Server from one server to another, you will need to use the Import and Export package feature in SQL Server Management Studio (SSMS) or the dtutil command line utility. The Import and Export package feature in SSMS is GUI-based. It cannot be used to copy or move multiple packages at the same time. The dtuil command, however, can be incorporated into a script to handle multiple packages in a batch.

When copying SSIS packages, we also need to copy the security permissions associated with the packages. The access to packages stored in SQL Server is controlled by database roles in the msdb database, including the three fixed database-level roles db_dtsadmin, db_dtsltduser, and db_dtsoperator. By default, the permissions of the db_dtsadmin, db_dtsltduser, and db_dtsoperator fixed database-level roles apply to the reader role for packages, and the permissions of the db_dtsadmin role apply to the writer role. However, you can change the default behavior by using SSMS. For example, if you want everyone in the db_datareader to be able to view a package, you can map the reader role of the package to db_datareader. When we copy a package to another server, we also need to copy its reader and writer roles. On a side note, because you can only connect to Integration Services with Windows authentication, these roles only apply to Windows logins in most cases.

Let’s first look at a scenario in which the packages are stored in SQL Server with the “Rely on server storage and roles for access control” protection level. This means, the contents of the packages are only protected by the database roles. The copySSISpackages.sql script shown below creates a stored procedure called copySSISpackages. The stored procedure accepts parameters of the connection information to a source server and a destination server, and then generates a script that can be used to copy SSIS packages from the source server to the destination server.

--=====================================================================
-- Author:  Yan Pan
-- Description: This stored procedure is used to generate a script 
-- for all the SSIS package stored in a SQL Server 
-- that are only protected by database roles.
-- The output script can be executed on another server 
-- to copy SSIS packages from the source server.
--=====================================================================
USE msdb
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[copySSISpackages]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.copySSISpackages
GO
CREATE PROCEDURE dbo.copySSISpackages 
@srcServer sysname, -- Source server name
@destServer sysname, -- Destination server name
@srcUser sysname = '', -- SQL Server login used to connect to the source server
@srcPassword sysname = '', -- Password of the SQL Server login on the source server
@destUser sysname = '', -- SQL Server login used to connect to the destination server
@destPassword sysname = '' -- Password of the SQL Server login on the destination server
AS
BEGIN
  SET NOCOUNT ON;
  
  print 'USE msdb'
 
  -- Copy SSIS packages
  select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /COPY SQL;' + 
  case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
  + ' /SQL ' + case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
  + ' /SOURCESERVER ' + @srcServer 
  + case @srcUser when '' then '' else ' /SourceUser ' + @srcUser + ' /SourcePassword ' + @srcPassword end
  + ' /DESTSERVER ' + @destServer
  + case @destUser when '' then '' else ' /DestUser ' + @destUser + ' /DestPassword ' + @destPassword end + ''''
  from msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90 fld
  on pkg.folderid = fld.folderid
 
  -- Copy SSIS package roles
  select 'EXEC [dbo].[sp_dts_setpackageroles] @name = ''' + pkg.[name] + ''', '
  + '@folderid = ''' + CONVERT(CHAR(36), folderid) + ''', @readrole = ' + isNull('''' + p1.[name] + '''', 'NULL') + ',  '
     + '@writerole = ' + isNull('''' + p2.[name] + '''', 'NULL') 
  FROM msdb.dbo.sysdtspackages90 pkg 
  left join sys.database_principals p1 on p1.sid = pkg.readrolesid and p1.[type] = 'R'
  left join sys.database_principals p2 on p2.sid = pkg.writerolesid and p2.[type] = 'R' 
END
GO

For example, we would like to copy the packages from the default instance on the host PowerPC to the named instance INSTANCE1 on the same host. On the default instance PowerPC, there are three SSIS packages – CreateLists, ImportCustomers, and BackupUserDB under Maintenance Plans (see Figure 1). The CreateLists package has the db_datareader role as the reader role, and db_datawriter as the writer role. The other two packages have the default package roles.

We run the copySSISpackages.sql script on the default instance PowerPC and create the copySSISpackages stored procedure in msdb. Then we execute the stored procedure.

exec dbo.copySSISpackages 'PowerPC', 'PowerPC\INSTANCE1'

We are using Windows authentication with dtutil here. Therefore, we don’t need to pass the user names and passwords. You can also choose to use SQL Server authentication. The output script from the stored procedure is shown here.

USE msdb
-----------------------------------------------------------------------------------------------------------------------------
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"CreateLists" /SQL "CreateLists" 
  /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"ImportCustomers" /SQL "ImportCustomers" 
  /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"Maintenance Plans\BackupUserDB" /SQL "Maintenance Plans\BackupUserDB" 
  /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1'

-------------------------------------------------------------------------------------------------------------------------------
EXEC [dbo].[sp_dts_setpackageroles] @name = 'CreateLists', @folderid = '00000000-0000-0000-0000-000000000000', 
  @readrole = 'db_datareader',  @writerole = 'db_datawriter'

EXEC [dbo].[sp_dts_setpackageroles] @name = 'ImportCustomers', @folderid = '00000000-0000-0000-0000-000000000000', 
  @readrole = NULL,  @writerole = NULL
EXEC [dbo].[sp_dts_setpackageroles] @name = 'BackupUserDB', @folderid = '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8', 
  @readrole = NULL,  @writerole = NULL

Copy the output script and run it on the named instance PowerPC\INSTANCE1. All three packages are copied successfully (see Figure 2).

If the xp_cmdshell option is disabled on the destination server, you will not be able to run the output script. Run the following statements to enable the option.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

In addition to database roles, SSIS packages stored in SQL Server can also be protected with a user key or a password. Package data encrypted with a user key is encrypted with a key based on the user who created or imported the package. Only the user who created the package will be able to run the package. This is not very useful for a production environment. Therefore, in production environments, the entire SSIS package is usually protected with a password. Let’s consider a slightly different scenario in which all packages stored in SQL Server are protected with a password for both security and convenience. In this case, we need to modify the copySSISpackages.sql script to include the “Encrypt” and “Decrypt” options in the dtutil commands. The new script copySSISpackages_encryptedByPwd.sql is shown below. It creates a new stored procedure called copySSISpackages_encryptedByPwd.

--=====================================================================
-- Author:  Yan Pan
-- Description: This stored procedure is used to generate a script 
-- for all the SSIS package stored in a SQL Server 
-- that are protected by both database roles and a common password.
-- The output script can be executed on another server 
-- to copy SSIS packages from the source server.
--=====================================================================
USE msdb
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].copySSISpackages_encryptedByPwd') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.copySSISpackages_encryptedByPwd
GO
CREATE PROCEDURE dbo.copySSISpackages_encryptedByPwd
  @srcServer sysname, -- Source server name
  @destServer sysname, -- Destination server name
  @oldPassword varchar(20), -- The password used to encrypt SSIS packages on the source server
  @newPassword varchar(20), -- The password used to encrypt SSIS packages on the destination server
  @srcUser sysname = '', -- SQL Server login used to connect to the source server
  @srcPassword sysname = '', -- Password of the SQL Server login on the source server
  @destUser sysname = '', -- SQL Server login used to connect to the destination server
  @destPassword sysname = '' -- Password of the SQL Server login on the destination server
AS
BEGIN
  SET NOCOUNT ON;
 
  print 'USE msdb'
 
  -- Copy SSIS packages
  select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /COPY SQL;' + 
  case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
  + ' /SQL ' + case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end
  + ' /SOURCESERVER ' + @srcServer 
  + case @srcUser when '' then '' else ' /SourceUser ' + @srcUser + ' /SourcePassword ' + @srcPassword end
  + ' /DESTSERVER ' + @destServer
  + case @destUser when '' then '' else ' /DestUser ' + @destUser + ' /DestPassword ' + @destPassword end 
  + ' /Decrypt ' + @oldPassword + ''''
  from msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90 fld
  on pkg.folderid = fld.folderid
 
  -- Change the encryption password from @oldPassword to @newPassword on the destination server
  select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /SQL ' + 
  case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
  + ' /SOURCESERVER ' + @destServer 
  + ' /DESTSERVER ' + @destServer + ' /Decrypt ' + @oldPassword + ' /Encrypt SQL;' + 
  case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end
  + ';3;' + @newPassword + ''''
  from msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90 fld
  on pkg.folderid = fld.folderid
 
  -- Copy SSIS package roles
  select 'EXEC [dbo].[sp_dts_setpackageroles] @name = ''' + pkg.[name] + ''', '
  + '@folderid = ''' + CONVERT(CHAR(36), folderid) + ''', @readrole = ' + isNull('''' + p1.[name] + '''', 'NULL') + ',  '
     + '@writerole = ' + isNull('''' + p2.[name] + '''', 'NULL') 
  FROM msdb.dbo.sysdtspackages90 pkg 
  left join sys.database_principals p1 on p1.sid = pkg.readrolesid and p1.[type] = 'R'
  left join sys.database_principals p2 on p2.sid = pkg.writerolesid and p2.[type] = 'R' 
END
GO

Let’s assume the three packages in our example above are encrypted by a password “oldpwd”. We want to copy them from the default instance PowerPC to the name instance PowerPC\INSTANCE1 and then encrypt them with a new password “newpwd”. We run the copySSISpackages_encryptedByPwd.sql script on the default instance PowerPC and create the copySSISpackages_encryptedByPwd stored procedure in msdb. Then we execute the stored procedure.

exec dbo.copySSISpackages_encryptedByPwd 'PowerPC', 'PowerPC\INSTANCE1', 'oldpwd', 'newpwd'

The output script is shown here.

USE msdb
-------------------------------------------------------------------------------------------------------------------------------
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"CreateLists" /SQL "CreateLists" 
  /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1 /Decrypt oldpwd'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"ImportCustomers" /SQL "ImportCustomers" 
  /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1 /Decrypt oldpwd'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /COPY SQL;"Maintenance Plans\BackupUserDB" 
  /SQL "Maintenance Plans\BackupUserDB" /SOURCESERVER PowerPC /DESTSERVER PowerPC\INSTANCE1 /Decrypt oldpwd'

--------------------------------------------------------------------------------------------------------------------------------
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /SQL "CreateLists" /SOURCESERVER PowerPC\INSTANCE1 
  /DESTSERVER PowerPC\INSTANCE1 /Decrypt oldpwd /Encrypt SQL;"CreateLists";3;newpwd'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /SQL "ImportCustomers" /SOURCESERVER PowerPC\INSTANCE1 
  /DESTSERVER PowerPC\INSTANCE1 /Decrypt oldpwd /Encrypt SQL;"ImportCustomers";3;newpwd'
EXEC [master].[sys].[xp_cmdshell] 'dtutil /Quiet /SQL "Maintenance Plans\BackupUserDB" 
  /SOURCESERVER PowerPC\INSTANCE1 /DESTSERVER PowerPC\INSTANCE1 
    /Decrypt oldpwd /Encrypt SQL;"Maintenance Plans\BackupUserDB";3;newpwd'

--------------------------------------------------------------------------------------------------------------------------------
EXEC [dbo].[sp_dts_setpackageroles] @name = 'BackupUserDB', @folderid = '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8', 
  @readrole = NULL,  @writerole = NULL
EXEC [dbo].[sp_dts_setpackageroles] @name = 'CreateLists', @folderid = '00000000-0000-0000-0000-000000000000', 
  @readrole = 'db_datareader',  @writerole = 'db_datawriter'
EXEC [dbo].[sp_dts_setpackageroles] @name = 'ImportCustomers', @folderid = '00000000-0000-0000-0000-000000000000', 
  @readrole = NULL,  @writerole = NULL

As you can see, the script first copies the three packages with the “Decrypt” option and the decryption password “oldpwd”. Then it encrypts the packages with the new password “newpwd”. After that, it copies the package roles.

Conclusion

This article has illustrated how to copy SSIS packages stored in SQL Server and package roles between SQL Server 2005 instances.