Sql-server – Upgrading SSIS packages

To restate, you have packages in SQL Server 2008 R2 and you wish to migrate them to SQL Server 2012. The packages are generally export to "file format".

The only way to know if they upgrade successfully is to upgrade them and find out. The biggest challenge I recall was with packages that had Script Components (not Tasks) as the pipeline has some biggish structural changes. The other obvious caveat is if you used a custom third party/in house Task/Component. That'll require manual migration and modification of the packages in visual studio.

You have two options with your upgrade. SSIS now has two deployment models. Package deployment model, a.k.a. Classic/Legacy, which is what you have here. Packages can be deployed to file system or msdb, you control your configuration, logging, versioning, etc.

Project deployment model is the new hotness. There is a dedicated SSISDB catalog that handles all the configuration, logging, versioning etc for you. I generally favor it over the former as it provides a better management and execution experience. Migrating package deployment model projects to project deployment model requires changing the packages themselves. The upgrade wizard will help in that but it will strip out logging and configuration as it wants you to use the new way. This will require more testing than the simple smoke test required with a package deployment model upgrade path. Whether it's worth the effort, especially in a change control heavy environment is best answered by you.

An approach for package deployment upgrade

dtutil.exe is the command line tool for deploying SSIS packages. Assuming standard install path, you'd be looking for the dtutil that is in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe to extract the packages as is. When a newer version of SSIS tooling touches a previous version package, the first thing that happens is an in memory upgrade to that version of SSIS. I mention this as if you didn't use source control, you might want to extract the packages "as is" out of the existing 2008 R2 instance before deploying to your 2012 instance. I have a script for that

;
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
    'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath LIKE '\SERV%';

If you like to live dangerously, you could change the final SELECT to skip the file system and directly deploy to the other server. This assumes you've already created the same folder structure over there.

'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /destserver MyShiny2012Box /copy SQL;"'+ F.FolderPath + '\' + P.PackageName + '"' AS cmd

I've eyeballed the above and it looks approximately correct but you should test it. It should render a command similar to the following

dtutil /sourceserver localhost /SQL "\SERV-ABC\Package" /destserv MyShiny2012Box /copy SQL;"\SERV-ABC\Package"

For that to work, you'd need to make sure dtutil is the 2012 version which should be in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe

From the comments

Don't confuse the tooling with the installed products and/or servers. Starting with 2005, there is a version of SSMS that was released with each SQL Server version (this gets murky as of SQL Server 2016). SSMS has an "object explorer". The Database Engine portion of Object explorer can be used to manage all versions of SQL Server and the common recommendation is to always use the latest version of SSMS in your environment as it'll have the most bug fixes etc.

Where this advice can fail you if you use the "other" options for Object Explorer. SSIS is the only thing I use with regularity so I'll address that.

You must use the version of SSMS that matches your target SQL Server instance for it to "work." In the screenshot, you're attached to the 2008 R2 server's Integration Services ... whatever that is but ignore it. Yes, ignore that blecherous tool as it's useless. It doesn't help you if you have multiple instances running and it only runs packages in ... 32 or 64 bit, I don't remember which.

Instead, use the one Object Explorer type that works across your instances - the database engine one. SSIS packages stored in "the database" are going to be in the msdb. Use the above query or go exploring those tables yourself. All the data is there. There are tsql procedures to move packages about, store them out, etc although I prefer dtutil myself.