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.