I'm trying to pass a variable value from SQL Server Agent job to SSIS package but the variable contains an apostrophe in it causing the SQL Server Agent job to fail
e.g In SQL Server Agent at Job Step Properties I'm entering the following details:
Property Path: \Package.Variables[User::VariableName].Properties[Value] Property
Value: Michael O'Callaghan.
Any idea how to resolve this issue?
Thanks for your all you suggestions but unfortunately they didn't work, however I built a clever workaround for this.
SQL server agent wraps a variable value in single quote e.g specifying Jon Doe in sql server agent, the agent wraps it like this 'Jon Doe' and passes it to the SSIS package, so if you were to enter a value with an apostrophe it would break the sql server agent job and won't execute the SSIS package it would look like this E.G passing this value: 'John O' Doe' this would cause the agent the job to break so you need to pass your variable value as : John O''Doe and the agent wraps it as follows: 'John O''''Doe' so you would need to include the following logic in your SSIS package:
Declare @TempVar nVarchar(50) SET @TempVar = REPLACE(?, '''''', CHAR(39))
The above code creates a variable to store the parameter value. It replaces the 4 single quotes to one. CHAR(39) is the ASCII representation of a single quote. This would then cause the variable value to look like John O'Doe. Hope this helps.
The reason I wanted to pass a variable value from the agent as I had to change the variable value very often from the SSIS package it would need to be deployed every time. So this way is faster.