Click here to Skip to main content
15,887,083 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Stored Procedure Syntax:
create procedure [dbo].[test_proc] @Message nvarchar(max)
as
begin
declare @SQLQuery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200) = '<abcde>'
SET @SQLQuery = 'DTExec /FILE ^"D:\SSIS_MSMQ_Package\msmq_new\msmq_sql\msmq_sql\Package.dtsx^" '
SET @SQLQuery = @SQLQuery + '/SET "\Package.Variables[User::vMessage].Value";"'+ @Message + '"'
EXEC master..xp_cmdshell @SQLQuery
End

SSIS package info:
SSIS package has a script task which contains the code for sending messages to MSMQ

Problem:
On exec [test_proc] ‘<xml></xml>’
Works fine

But On exec [test_proc] ‘
Error:
Message Text:<xml version=1.0>
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3460.0 for 64-bit
Copyright (coffee) Microsoft Corporation. All rights reserved.
NULL
Started: 8:32:58 AM
Error: 2015-02-06 08:32:59.43
Code: 0x00000001
Source: Script Task
Description: Exception has been thrown by the target of an invocation.
End Error
DTExec: The package execution returned DTSER_SUCCESS (o).
Started: 8:32:58 AM
Finished: 8:32:59 AM
Elapsed: 0.531 seconds
NULL

The problem seem to be passing special characters to the script task variable
Posted

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900