SQL Server Agent and SSIS packages

Question

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?

Answer 1

If the package is deployed to SSISDB and executed from there, use SSISDB stored procedures to set the value and escape the quote like how you would via T-SQL. The SQL Agent job can then use a T-SQL script for this step instead. The example below uses the set_execution_parameter_value stored procedure to set this value and will still result in "Michael O'Callaghan" being passed in.

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'Project Folder', @project_name=N'Project', @use32bitruntime=False, @reference_id=Null

DECLARE @var0 SQL_VARIANT = N'Michael O''Callaghan'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Name', @parameter_value=@var0

DECLARE @var1 SMALLINT = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

Answer 2

Escape it. Just use a double apostrophe. '' (Not a quotation ", but a apostrophe apostrophe).