SSIS 2012 – Export old version of a deployed project

New in the 2012 version of SSIS is a feature that lets us keep a user-configurable amount of deployed project versions in the catalog. The problem, and the point of this blog post, is that you cannot export an older version of a project without reverting back to that version. This may not be possible in your environment and this post is an effort to provide you with another option.

First, this is what I am referring to:

When you right-click on a project under “Integration Services Catalogs” in SSMS, you can choose to view its versions. From this window you can revert back to an older version while still retaining the latest version deployed.

If you want to export projects, you can do so by right-clicking on the project and selecting “Export…” In this window, just point it to an appropriate location. In my case, I saved it to Documents\SQLSaturday165. The resulting file you produce is an .ispac (Integration Services deployment package). A .ispac file is just a .zip. Rename it to use .zip instead of .ispac if you want to extract a single package out of a project.

If you want to export an older version of the project, you have two options – set the older version of the project to be the current version via the Project Versions window, or you can use an SSIS package to explicitly export the desired project version. I will demonstrate the SSIS package solution. The full sample and download links are at the end of the post.

Step One
Create a global, temporary table (##) on the SQL instance that has the SSISDB catalog.

CREATE TABLE ##projectBLOB (val VARBINARY(MAX));

Step Two
Open up the ExportProject.dtsx package and modify the OLE DB Source in the data flow to use the values from the following query:

SELECT ov.object_version_lsn AS project_version_lsn,
       ov.object_id AS project_id,
       *
  FROM internal.object_versions AS ov
  JOIN catalog.projects AS p
    ON ov.object_id = p.project_id
   AND p.name = N'SQLSaturday165_SSISDBInternals' -- replace with your project name
  JOIN catalog.folders AS f
    ON p.folder_id = f.folder_id
   AND f.name = N'SQLSaturday165_SSISDBInternals' -- replace with the folder name that contains your project
 ORDER BY ov.created_time DESC;
GO

Step Three
Edit the derived column component to set the path that you want to put the exported project in. Remember that in the derived column component you need to escape slashes. (“C:\Temp” should be written as “C:\\Temp”)

Step Four
Change the SSISDB connection manager object to point to your SQL instance containing the SSISDB catalog.

Step Five
Execute the package.

Step Six
Confirm your project was exported to the path entered in the derived column component. You should have a .ispac file with the name of your project in that location. From this point, you can double-click on it to launch the deployment wizard, or you can rename the file so that it has a .zip extention. If you choose to rename it to .zip, you will be able to individually pull out the packages.

Just a word of warning – THIS SSIS APPROACH IS NOT A SUPPORTED METHOD OF EXPORTING PROJECTS FROM THE CATALOG. YOU NEED TO BE A MEMBER OF SYSADMIN OR THE SSIS_ADMIN DATABASE ROLE. YOU TAKE SOLE RESPONSIBILITY FOR RUNNING THIS PACKAGE IN YOUR ENVIRONMENT. I AM NOT RESPONSIBLE FOR ANY DAMAGE THAT IS A RESULT OF RUNNING THIS PACKAGE. THIS IS AN UNSUPPORTED TECHNIQUE THAT USES INTERNAL, NON-DOCUMENTED PROCEDURES FOR EXPORTING PROJECTS.

With that said, I am merely posting this technique for educational purposes. The supported method for exporting an older version of a project is to revert to the older version and exporting it using the GUI in SSMS and then reverting back to the correct, current project.