Parameter variables are read-only on SSIS package?

Question

I defined parameters as shown below on my SSIS project

then I would like to change those optional over my sql agent- job, i defined as below.

even I tried to set a fix date value instead of getdate but It returns an error message telling me that

Error: 2014-12-19 16:30:21.56 Code: 0xC001F016 Source: Package Description: Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only. End Error DTExec: Could not set \Package.Variables[startdate].Value value to 18.12.2014. Started: 16:30:21 Finished: 16:30:21 Elapsed: 0.296 seconds. The package execution failed. The step failed.

are the parameters read only? if yes, what is the sense of using paramters if i can only set them inside the projects. there are variables, they will do the same job. If I am doing something wrong, please give me some light?

I tried to add getdate() instead of fix value already on project level rather than inside sql job but It is not allowed as well. why?

thanks.

Dad

Did you find any solution to this? – Loreenlorelei

@VinShahrdar unfortunately no but i has been long time since maybe never versions have it. – Dad

Answer 1

I get my research via this video: https://www.youtube.com/watch?v=wBpBTl_vUBM

According to the video (start at 15:14), the project parameters is seem like just read only and package variables can be used to either read or write.

Trelliswork

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Fortaleza

Answer 2

I just came across the same thing in VS2017 with a package on compatibility level SQL Server 2012. I had to remove all references of the variable from the Execute SQL Task, delete it from the variable list on the package, and then recreate the variable via the Execute SQL Task as a new variable. This resolved the issue for me. I think its a bug though because I could even see that the ReadOnly property of the variable was in fact set to False. Playing with this flag did nothing.

Edit: Found that the variable name had a 1 appended to it and was not using the variable that I had intended. I could not find the variable name in the Variable list in the package editor, so I opened it up in notepad and found that the ReadOnly flag for the variable was actually set to True. Changing the flag fixed the issue.

Auroora

Answer 3

Create a User Variable instead. They are located under the unintelligible eraser looking box top right of package editor. The tooltip just says “Variables” but they are stored in User namespace instead of $Package, so you can access them as User::varname within the project for parameter mapping, and as @[User::varname] in expressions, and finally as (Case Sensitive) \Package.Variables[User::varname].Properties[Value] from sqlagent job step Set Values tab. The XML is even different from the above. However it’s easier to generate a Package Configuration file using Visual Studio where you can select the variable overrides. If you need the path for a hand crafted command line, it will be there for you in the dtsConfig file.

Reathareave

Answer 4

I'm not entirely sure I follow your question but you can solve this problem by making those variables and expression with that value like this.

Avivah

But this variable. I'm actually talking about parameters which is supposed to be accessible outside of SSIS package. – Dad

Answer 5

you could pass the parametter value to a variable, make the caluclations you need with the variable, then use the variable in the package.

Arvind

It is very nice advice but my problem is to access the parameter from outside of SSIS project and assign a value for it. As I know variables cant be accessed outside of the project, right? – Dad

To assign values from outside the package one option is to use parametters, another option would be to use a table you can update from outside the package as well as from inside the package; also you you could use an xml file, or any other data source you have at hand. – Arvind