Sql-server – Moving SSIS Packages to New Server

migrationsql serversql-server-2008-r2ssis

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2.
We are migrating to a new server host but remaining on SQL Server 2008R2.

How can I copy/migrate these SSIS packages to the new server? Is Dtutil the best option?

Best Answer

Yes, DTutil is still going to be the best option. I have a post, SSIS Package Extract from MSDB on how to use dtutil + the data in the packages table to generate the call(s) to export to disk. Instead of the file destination, you would substitute sourceserver. If you've used folders to organize your packages, then you'll need to create them beforehand.

Script posted here for posterity. This applies for 2008+ packages or 2012/2014 package that have used the package deployment model. With 2005, sysssispackagefolder would be replaced with sysdtspackages90 and sysssispackagefolders with sysdtspackagefolders90 

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    -- File extraction command line
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
    -- Move Package command line
    -- 'dtutil /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@X /DestServer <servername> /MOVE SQL;"'+ F.FolderPath + '\' + P.PackageName + '" /DESTUSER destUserName /DESTPASSWORD !38dsFH@v' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath <> '\Data Collector'
Related Question