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:
- If you accidentally delete an environment variable that was previously mapped to a previous version, any deployed SSIS project version will fail.
- 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.
I have come up with a couple of workarounds, should you face a similar scenario (unless this issue is fixed in the future).
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.
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.
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).
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!