Editing Published SSIS Package in SQL Server 2012

18/04/2015 15:27

Editing Published SSIS Package in SQL Server 2012

So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can't easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment  there is no way of just quickly downloading that single SSIS package.

SQL Server 2012 Integration Services now is now making use of  Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two "easy" ways of doing so.

A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.

B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).

Caveat: Both these options require access to the project on the server.

Option A: New Project

1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence Integration Services on the left navigation pane.

2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.

Integration Services Project Wizard

3. Click Next on the Integration Services Import Project Wizard window

SSDT - Integration Services Project Wizard - Introduction

4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.

SSDT - Integration Services Project Wizard - Select Source

SSDT - Integration Services Project Wizard - Select Project

5. Follow the rest of the instruction.

SSDT - Integration Services Project Wizard - Validation

SSDT - Integration Services Project Wizard - ReviewSSDT - Integration Services Project Wizard - Results


6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have  4 connection manager files and 9 dtsx files.

SSDT - Integration Services Project Wizard - Project Created

This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.

Option B: The shortcut

1. Navigate to the SSIS project on SSMS; i.e.  [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.
SSMS - Export SSIS Project

2. Right click on the [Project Name], then choose Export....

3. Choose the destination folder on "Save As" to save the .ispac file

4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.

5. Browse the zip file and you should be able to see the .dtsx file(s).  Please note that in this example, I have 4 connection manager files and 9 dtsx files.

Browse zip file that was renamed from ispac

6. Now you can extract the desired .dtsx file(s) from the zip file.

Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.

Special thanks to Josh Fennessy (b ' t), for providing Option B.

Would it work for SSDT Visual Studio 2012?

Update - 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools - Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.

SSDT - What it looks like in Visual Studio 2012

Wrap Up

Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.

This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.


Crie um site com

  • Totalmente GRÁTIS
  • Centenas de templates
  • Todo em português

Este site foi criado com Webnode. Crie um grátis para você também!