Oct 122012
 

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:
SSIS 2012 - Catalog Project Versions

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”)

SSIS 2012 Catalog Derived Column Changes

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

SSIS 2012 Catalog SSISDB Connection Manager Changes

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.

SSIS 2012 Catalog Project ispac file

The resulting .ispac output file from the export process.

SSIS 2012 Catalog .zip file

Renaming the .ispac file to .zip allows you to inspect its contents.

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.

  3 Responses to “SSIS 2012 – Export old version of a deployed project”

  1. [...] Difficult to extract a prior version (see http://www.ssistalk.com/2012/10/12/ssis-2012-export-old-version-of-a-deployed-project/) [...]

  2. FWIW, I was trying to do something similar, and asked Jamie Thomson on twitter, who forwarded me on to Matt Masson who works on the SSIS team. Who responded thusly:

    @Mbourgon @jamiet you can use catalog.get_project to grab proj file. pkgs aren’t stored separately. you’d need to parse with SSIS OM

    Looking to figure out what “OM” was, I found the SSIS Managed Object Model (aka ManagedObjectModel or MOM), which is an SSIS API that’s reachable via Powershell.

    Matt also wrote this article, which does some explanation on how to deploy TO SSISDB using MOM via PoSh, but not how to get it out. I’m trying to figure out that next. I’ll let you know if I get anywhere with it. (and please let me know if you do)
    http://blogs.msdn.com/b/mattm/archive/2011/11/17/ssis-and-powershell-in-sql-server-2012.aspx

  3. each time i used to read ?maller posts w?ic? also ?lear their
    motive, and th?t i? alo happening ?ith thi? pragraph w?ich I am reading here.

    M? web blog :: Russia business database

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>