SQL Server Agent and SSIS packages

  • A+

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?


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 


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: