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.

 

Further Reading

Can I deploy a single SSIS package from my project to the SSIS Catalog? by Matt Masson

Short Review on .ispac SSIS Deployment by Julie Koesmarno – my thoughts on Matt’s article above.

What’s New (Integration Services) on MSDN

 

 

SSIS Reporting Pack – High Level Review

I always appreciate codeplex tools that are available out there to help us Database Developers and Administrators in making our lives much easier. One of my favourite codeplex tools is SSIS Reporting Pack written by Jamie Thomson (blog | twitter). The reason is simple, I use SSIS 2012 a lot this year for the company that I work for; and I find SSIS Reporting Pack makes it easier for me to see the progress of the SSIS projects that I have scheduled to run.

In addition, when there is an issue, (e.g. SSIS package just hung due to ASYNC_NETWORK_IO issue on the source database server), it’s much easier to see when it happened and what the last messages were. Of course, in this example, it wouldn’t say anything that there was an ASYNC_NETWORK_IO issue on the source database server, but the ability to visually see what’s happening and quickly narrowing down the result is the key. The tool is written using SSRS, which means I can also export it to Excel, PDF, etc, if I want to.

The SSRS reports accesses the SSISDB database of the server specified in the “SSISCatalog.rds” Shared Data Source. So, it can only track as far as the data retained in SSISDB. If you have multiple SSISDB instances, you will need to run/deploy separate SSIS Reporting Pack reports for each instance. Now, let’s talk about my favourite features within SSIS Reporting Pack.

 

Dashboard

It’s simple and easy to see what SSIS projects that have been running on the server and their execution time. This gives me the ability to quickly see how my deployed projects are running in recent times.

From here you can go to “Most Recent Execution” , “Most Recent Failed Execution”, “Folders”, “Executions”, “Longest Executions”.

SSIS Reporting Pack - Dashboard

SSIS Reporting Pack - Dashboard

By the way, if you have any suggestions on what else to put on the dashboards, please log it here at codeplex.

 

Executions Report

This gives me a quick view of how the executed packages perform within the selected period. I can also narrow it down to only show a specific project or folder. This is a more powerful version of the dashboard with more information to start doing analysis. For example, how often a project fails and see if there is any pattern. You can drillthrough to the project execution that failed (or ran successfully) which will bring up the Execution Report.

 

SSIS Reporting Pack - Executions

SSIS Reporting Pack - Executions

 

Execution Report

The Execution page provides a detailed list of event messages associated to the Execution. It’s a great way for debugging at package and/or task level. Browsing it by drilling down the package executions to the task level is made easy in this Execution page. In addition, you can also browse previous or next execution of the same package; which is very handy if you have deployed a new version of the package and would like to analyse how they compare.

SSIS Reporting Pack - Execution

SSIS Reporting Pack - Execution

 

Wrap Up

So, if you are using SSIS in SQL Server 2012, give SSIS Reporting Pack a go, which you can download from  http://ssisreportingpack.codeplex.com/

The latest version, which is v0.4 is stable. SSRS Report Server is not required as you can run the report from SSDT. However, having it published to an SSRS Report Server is a better idea. Running the report can sometime take a long time.

If you have used SSIS Reporting Pack, please share your thoughts and comments here.

 

Further Reading

SSIS Reporting Pack v0.4 – Execution Report updated by Jamie Thomson

SSIS Reporting Pack v0.2 now available by Jamie Thomson

SSIS Reporting Pack – Installation Overview at Codeplex

 

Short Review on .ispac SSIS Deployment

My last two articles on SSIS have been related to bugs. That’s not because I am looking for its faults all the time. I like using SSIS – I have been since SQL Server 2005, more so in 2008 and even better in 2012. I think SSIS has limitations but also great advantages. For most of the projects that I have worked with, it simplifies quite a lot of things for me.

Matt Mason (twitter | blog) has discussed the branching/development strategies and how they fit in the revamped SQL Server 2012 SSIS in a couple of articles. Read them here:

Thoughts on Branching Strategies for SSIS Projects

Can I deploy a single SSIS package from my project to the SSIS Catalog?

 

So here’s my thought on the new .ispac method of deployment in SQL Server 2012, in response to the latter post by Matt.

After using SSIS in SQL Server 2012 for a moderately sized ETL for data warehouse for over 6 months, I’ve become more and more appreciative of the .ispac method of deploying. Building/deploying it, as if it was a C# project, in SSIS SQL Server 2012 makes more and more sense to me.

There has been a few seconds in the past, which I thought deploying just a single package would be useful. For example, when there is a need to import simple text files that can be done via one SSIS package on an ad hoc basis. However, the more I think about this, I think it’s not a “good” enough reason to do a single package deployment. In fact, if it gets run reasonably frequently or it may be reusable, I would create a project called “Ad hoc Import” which consists of a collection of “common” ad hoc SSIS packages.  In my experience, I don’t always know how often an SSIS package to be executed at conception. It could be a one-off / throw-away package; or it could start with being a one-off and then continue to be executed every so often. More often than not, my “first thought” one-off packages have become reusable (re-runnable) that they are worthy of being maintained as a project and deployed to the Integration Services Catalogs.

I think having / forcing project deployment just makes us think twice about how we organise our packages / projects in Integration Services Catalogs; which in my opinion, is not a bad thing at all.

Also – yes – I do use version control for all my SSIS projects. I use Team Foundation Server 2010. Although with the Integration Services Catalogs in SQL Server 2012, the deployed versions information is kept, this is not good enough to replace a version control tool; especially in team development environment.

Most of the issues that I have encountered and submitted at Microsoft Connect in regards to administering and executing deployed SSIS projects have workarounds (albeit risky). So I am all for project deployment.  By the way, more info on these issues:

http://connect.microsoft.com/SQLServer/feedback/details/751220/ssisdb-package-parameter-error-in-previous-version-project-lsn-carried-forward-to-the-future-deployed-versions. Also see: SSISDB Bug: Package Parameter Mapping

http://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter. Also see: Get Parameter Values from SSISDB Catalog: a bug?

I am also keen to hear what others think about a single package deployment and/or their experience with the new project deployment method in SQL Server 2012.

 

 

 

 

SSISDB Bug: Package Parameter Mapping

I have recently posted a new SSISDB bug at Microsoft Connect relating to Package Parameter mapping.  Running an SSIS project where the previous deployed version had an issue with the mapping of child package parameter, will carry forward the issue to the future deployed versions (even though it has been remapped properly in the future versions).

The unexpected error message will look like this:

Call Child Package:Error: Error 0×80131500 while executing package from project reference package “Child.dtsx”. There is no appropriate variable in the current environment to map to the parameter “paramChild”..

After a bit of investigation, it seems that the underlying stored procedure is looking at package parameters (mapped or unmapped) of all deployed versions of the project. Meaning that:

  1. If you accidentally delete an environment variable that was previously mapped to a previous version, any deployed SSIS project version will fail.
  2. If your previous version had incorrect mapping and failed, the current and future deployed versions will always fail, unless you deploy a change to ensure the previous version will run properly.
SSMS Integration Services Project Versions Dialog Box

Project Versions Dialog Box in SSMS

Workarounds

I have come up with a couple of workarounds, should you face a similar scenario (unless this issue is fixed in the future).

Workaround #1

1. Revert back to the old SSIS project version that is having the issue, and fix the mapping of the child package to the environment variable.

2. Repeat the above step for all SSIS projects that may have the issue.

3. Revert back to the latest version of SSIS project.

Caveat: if the variable is deleted from the environment for a reason, this needs to be kept instead; for the sake of rectifying the issue with older versions of the project AND to retain the history of older versions. This will make it harder for administering and managing SSIS project deployment and maintenance.

 

Workaround #2

1. Abandon / delete the SSIS project that is having issue.

2. Redeploy the project with the same name, and reconfigure the child package parameter to the environment variable properly.

Drawback: History of the SSIS project versions is not retained in the Integration Services Catalog.

 

Permanently Fixing This Bug

I think this is a real problem that most people will run into. Unless the solution is “don’t use package parameter” – which is not ideal – this problem should be fixed.

How?

Well, first, Microsoft SSIS team should hire me so I can help fixing it! ;) Or, you could just vote for the bug at Microsoft Connect here, and wait until it gets fixed.

In the mean time, I have come up with an alternative solution which I have to put a strong disclaimer so you should use this at your own risk. I will not be responsible if you deploy this in production. Here are the steps to permanently fix the issue:

1. Create a backup of your SSISDB.internal.get_project_parameter_values by scripting it out from the SSISDB database. Save this as a file somewhere safe.

2. Open my version of SSISDB.internal.get_project_parameter_values.

3. Comment out the CREATE PROCEDURE line.

4. Uncomment the ALTER PROCEDURE.

(Step 3 and 4 are deliberately put in place here, so you won’t accidentally overwrite the original stored procedure).

 

Wrap Up

Package parameter that is to be mapped to an environment variable should be use with caution. Deleting an environment variable that is mapped to a previous deployed version and running the properly mapped parameter of a new version will cause an unexpected error “There is no appropriate variable in the current environment to map to the parameter”. I have stated a number of workarounds above, please use them wisely. The fix that I have proposed on altering internal.get_project_parameter_values is not 100% tested and should only be used in development environment.

To have this bug fixed properly, please vote at Microsoft Connect site.

Comments and feedbacks are welcomed!

Get Parameter Values from SSISDB Catalog: a bug?

catalog.get_parameter_values

Recently I have been working with a couple of SSIS projects on SQL Server 2012. During this time, I have managed to encounter an issue with SSISDB. This issue is quite trivial; however it can be important for querying/reporting deployed SSIS packages. In fact, I found this issue because I wanted to create a deployment script to configure Environment and SQL Job to run the SSIS project referencing the configured environment.

The issue is to do with SSISDB.catalog.get_parameter_values.

As documented on Books Online (http://msdn.microsoft.com/en-us/library/ff878039), to obtain all project parameters and the parameters from all packages, when executing catalog.get_parameter_values, the “package_name” input parameter should be set to NULL.

** Excerpt from BOL – catalog.get_parameter_values **

However when running the catalog.get_parameter_values stored procedure with @package_name set to NULL, it returns an error instead stating “The input parameter cannot be null”.

Msg 27138, Level 16, State 1, Procedure get_parameter_values, Line 18
The input parameter cannot be null. Provide a valid value for the parameter.

 

I have logged this as a bug at Microsoft Connect:

https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

 

My own version: catalog.get_any_parameter_values

As a temporary workaround, I have a modified version of catalog.get_parameter_values where it is possible to retrieve all project parameters and package parameters in a single stored procedure call.  I’m calling the new modified version, catalog.get_any_parameter_values which can be deployed to SSISDB and consumed in custom queries. Any built-in reports in SQL Server 2012 that may be calling catalog.get_parameter_values stored procedure will not be affected.

Please note that catalog.get_any_parameter_values is a draft version. Deploy and make use of it with care. This is intended to be as a supplement for administering Integration Services Catalog.

Example of usage:

USE [SSISDB]
GO
EXEC [catalog].[get_any_parameter_values]
    @folder_name = 'AdventureWorks BI',
    @project_name = 'AdventureWorks BI ETL',
    @package_name = NULL,
    @reference_id = NULL

 

The results will be similar to the following:

To deploy the script:

  1. Download catalog.get_any_parameter_values from here.
  2. Run the script against SSISDB database in a SQL Server 2012 Database Instance.  The catalog.get_any_parameter_values stored procedure will be created and will only work within the target Integration Services Catalog. If there are multiple servers running Integration Services Catalog, the script needs to be deployed against SSISDB database of each server.

Compatibility level: SQL Server 2012 RTM (11.0.2100)

If you have any feedback on my catalog.get_any_parameter_values stored procedure, please let me know. More importantly, please vote for the bug ticket relating to catalog.get_parameter_values at Microsoft Connect. https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

Other Workaround

An alternative workaround to the issue in catalog.get_parameter_values is to call the catalog.get_parameter_values once per project and per package in the project. I think this is a little inefficient but will do the job well with little risk on the SSISDB.