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 0x80131500 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.
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!
9 Responses
[…] 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 […]
Great post! Thank you… this saved me from hours of figuring this out 🙂
Maja,
I’m glad that it helps. Thank you for the feedback!
Julie
Julie, great post and great workaround too, but how can i discover which version i need restore?
And , if i submit some versions and the collector drop my “good version” … the only resolution is project delete and try again..with correct actions only ?
Hugs
Leo Matos
Hi Leo,
Apologies for the delayed reply.
As far as I know, there is no easy way of knowing which version to revert back to. I tend to keep note of either of the following items on the Versions window of the SSIS Project in the SSISDB Catalog via SSMS:
You can also change the Description value from Project Properties in Visual Studio to mark the version that you potentially want to revert back to. This value here is captured in the Versions of SSISDB catalog. It may be better to rely on source control to keep track of the version that you want to revert back to (i.e redeploy the project).
I am going to post something around this shortly. Once again, I apologise for the delay in getting back to you.
Hope this helps,
Julie
Came across your post while looking for something similar. I’m not even using Environments, I’m passing parameters using set_execution_parameter_value in an agent job.
My timeline is;
1) Deployed package from svn March
2) Package ran until a week ago, then broke due to parameters not being set (and hence reverting to design-time value, in this case a file path)
3) Wasted a week figuring out what’s going on
4) Redeployed package from March
5) Success…..
Hi Gavin,
Thank you for stopping by and sharing the tips!
Passing in parameters directly (without using Environments) is a valid and functional way of executing SSIS. I agree 🙂
I personally prefer to use Environments as it allows me to have a number of predefined sets of parameters, which makes it easier for me to test different values, etc.
Julie
Old post, but I came across a similar sounding problem in SQL 2014 and since this post was the only thing remotely related on the internet I thought I’d share my problem/solution so if others find this post after searching for the same problem, I might be of help.
Problem: I deleted a required project parameter in Visual Studio, but after deployment to SSISDB, the project parameter was still showing in SSISDB (and the agent job wouldn’t run without a value being set).
Solution: The way I fixed it was to recreate the parameter in Visual Studio, make it not required and deploy it. After I deployed it, I then deleted it in VS and deployed it again.
Might have been a freak thing, but thought I’d share.
PS: This post was very prescient about Microsoft hiring you!
Thanks for sharing Leonard 🙂 I’ll make sure I’ll pass it on to the team!
I’m very honoured and humbled that Microsoft hired me!
Julie