I have read through many of these topic and still no luck with this:
My situation is that I have a DTS package with a Execute SQL Task. The
task executes a stored procedure which has one input and one output
parameter:
EXEC Update_SupplierParts @FileName, @Return = @Return OUTPUT

My stored procedure looks like:
ALTER PROCEDURE [dbo].[Update_SupplierParts]
@FileName VarChar(255),
@Return VarChar(255) OUTPUT
AS
......

Right now @FileName is hard coded into the SQL task; I would like it to
use a global variable instead of a local variable. I currently have an
ini file setup with a dynamic properties task to read in a few settings
and store them in global variables.

I would like to be able to do something like:
EXEC Update_SupplierParts ?, @Return = @Return OUTPUT

or even SET @FileName = ?

I have found:
EXEC sp_foo ?, ?, ?
will work perfectly fine as long as I do not declare any local
variables. But as soon as I declare a variable (even if it is not
used) the task will not parse and return an error saying "Syntax error
or access violation".
I have tried the SET NOCOUNT ON mentioned before, with no luck. I am
looking for a simple solution, preferably not having to deal with VB or
ActiveX scripts, as the SQL task is very long and dynamic SQL with
ActiveX would probably not be possible.

Any ideas on how to fix this would be greatly appriciated!
Thanks

Have you looked at this yet?

http://www.sqldts.com/default.aspx?234

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


<wizkid8586 (AT) gmail (DOT) com> wrote

Allan,
I had read that article already, but after closer inspection, it helped
me solve the problem. I'll explain what I did below in case anyone else
has this same problem.

What I ended up doing was opening up disconnected edit and selecting
the Execute SQL Query Task that i needed the global variable for.
There is a property for InputGlobalVariableNames, it takes a semicolon
delimited list of global variables like:
"gvFileName";"gvReturnVal";"etc"

I put my global variable there, and then opened the SQLStatement
property.
I change my SQL script to reflect the new global variable like so:
SET @FileName = ?

This will now set the global variable I defined earlier to the local
variable @FileName, which can be used like normal.

I hope this helps anyone else!
Thanks again, Allan