Friday, March 4, 2016

Error SSIS Variable (Cannot assign value to variable): The result of the expression cannot be written to the property. The expression was evaluated, but cannot be set on the property.

I recently encountered an error while dynamically returning the value of a connection string in a parent package used to execute child packages in a for-each loop.The parent master package retrieves the name of the child packages from a database table and passes the value to a connection string variable(user::packagename) in the for each loop container,each enumerator of which is the package name for the subsequent execute package task in the container.

However the package on runtime failed with the error "The result of the expression “@variablename” on property  cannot be written to the property. The expression was evaluated, but cannot be set on the property."

When a script task was used to debug the issue it correctly showed all the package names in the loop which proved that the value to the variable was being written correctly...However when the script task was replaced by execute package task it failed with the above error.After much troubleshooting found out that the value of the string user:packagename must be initialised to an existing connection manager in the parent package before executing it .Once that was done the parent package worked perfectly executing the child packages with the dynamic connection string variable.

1 comment:

  1. I think SSIS and other tools actually provide the best way of looking for finding information about many aspects.

    SSIS Upsert

    ReplyDelete