Recently I’ve been getting the following error message while trying to create an SSIS 2012 step in SQL Server Job Agent, and setting the Parameters or Connection Strings for that step:
The property ‘ParameterName’ contains invalid characters as an object name. Remove the invalid characters. (Microsoft.SqlServer.Management.IntegrationServices)
This error emerges after confirming the newly created step (which will run the SSIS package) in SQL Server Job Agent, and after setting the parameter.
In order to replicate this issue, you’ll need to have an SSIS package deployed in a SSIS Catalog DB, then you can create a new SQL Server Agent Job, create a new step of type “SQL Server Integration Services Package” and reference your SSIS package in the SSIS Catalog DB, you’ll need to set one SSIS package Parameters or Connection Manager properties using a character that can be invalid during encoding to XML, for example an Exclamation Mark (!).
As you have probably figured out, the underlying issue here is an encoding problem when SQL Server Job Agent is trying to serialize the Parameter or Connection Properties to be stored for as parameters when executing the SSIS package; SQL Server Job Agent is not encoding the parameters when building the parameters XML, hence leading to the error above.
To resolve (or actually by-pass) the invalid character issue, you can instead of setting the SSIS Execution Parameters through SQL Server Job Agent, use the Configure option on the SSIS package within the SSIS Catalog DB, as shown in the image below.
This uses a different mechanism to store the SSIS parameters, which in-turn by-passes the encoding issue if you have any characters that might invalidate an XML document. The SSIS package could now be run through Job Agent without explicitly setting the parameters (inherited from the configuration settings in the SSIS Catalog DB).
Obviously this has major limitations, since sometimes you might need to change the SSIS parameters when executing under the context of different Job Agent jobs, if that is the case then you’ll either need to try and not use any invalid XML characters, or apply some encoding function within the SSIS package itself and supply encoded parameters to the job agent. If you manage to figure-out a way around this please share it in the comment section below.
Am sure the SQL Server dev team will catch on and resolve the issue, I might stick a ticket in Connect if it starts to really get in the way.
SSIS The property ‘ParameterName’ contains invalid characters as an object name rated 4 out of 5 by 2 readers