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?

userfl89 · Answer 1 · 2019-02-20T17:31:18.003

5

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

edited Feb 20 '19 at 17:31

answered Feb 20 '19 at 17:08

userfl89

4,610
1
9
17

Why not use stored environment variables? – J Weezy Feb 20 '19 at 18:10
Good point @JWeezy, setting this from an environment variable would work too. – userfl89 Feb 20 '19 at 18:16
@JWeezy it is good to add this as another answer, so the OP has all possible solutions, or you can edit the following answer and add this suggestion – Hadi Feb 21 '19 at 00:13
1

@Hadi Per your recommendation, I have provided a separate answer. Thanks. – J Weezy Feb 22 '19 at 20:22

score 5 · Answer 2 · edited Feb 22 '19 at 20:27

5

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

edited Feb 22 '19 at 20:27

Hadi

36,233
13
65
124

answered Feb 20 '19 at 17:17

IamBatman

975
12
18

Srikar mogaliraju · Answer 3 · 2019-02-21T14:26:29.260

2

Try the standard way of maintaining a configuration file(if you are using 2008 or less) and pass the variables values through the file.

edited Feb 21 '19 at 14:26

answered Feb 21 '19 at 12:38

Srikar mogaliraju

215
1
10

SSIS Project deployment mode has own, modern way of configuration. It does not use configuration files anymore – Alexander Volok Feb 21 '19 at 12:39
@AlexanderVolok I was suggesting for 2008 version. Edited my answer. – Srikar mogaliraju Feb 21 '19 at 14:25
you actually right. OP tries to configure package that deployed in a "package mode"... I was confused by answers which using project deployment mode – Alexander Volok Feb 21 '19 at 15:59

score 1 · Answer 4 · edited Feb 22 '19 at 20:26

An alternative way to handle this, and frankly I think the best way, is to use Environment Variables. To my knowledge, this was introduced when Microsoft rolled out the project deployment model with SQL Server 2012 as a replacement to the package deployment model. The package deployment model required that package parameters be specified in a separate XML file to be deployed to the server. With the project deployment model, Microsoft has created a user-friendly user interface in SQL Server to manage this - the XML file has been removed.

In short, Environment Variables allow developers to link package parameters, but not package variables as those are internal to the package itself, to SQL Server and thus expose them on the server. This makes management of identical package parameters that exist across packages (e.g., connection managers, network folder locations in FQDN format, etc.) incredibly easy to manage. The idea here is that if packages need to be pointed to a new server or new network folder, then developers can simply change a single value in SQL Server, which would then propogate out to all packages without the need to open, change, and re-deploy the package.

For detailed steps on how to do this, see the following references:

Microsoft: This is a bit dry, but is comprehensive and from the horse's mouth.

SQL Chick: More intuitive and provides screenshots, which I found helpful.

Parameterizing Connections and Values at Runtime Using SSIS Environment Variables

score 1 · Accepted Answer · answered Feb 23 '19 at 21:57

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.

SQL Server Agent and SSIS packages

5 Answers5