Hi there,

I have a dts package that first pulls records into a temp table. I
also have a Global Variable of type boolean called ContinueProcessing.
Once the task runs that populates the temp table I have an Execute Sql
Server Task that checks to see if there are records and if there are it
should set the Global Variable to true. I then have a workflow activex
that if the Global Variable is true, it continues, otherwise there is
no reason to run the rest of the tasks so I set Main =
DTSStepScriptResult_DontExecuteTask. My problem is I keep getting an
error with my code in the Execute Sql server task.

If EXISTS (SELECT * from tempFile)
BEGIN
SET DTSGlobalVariables("ContinueProcessing").Value = True
END

At this step I get the following error:
Deferred Prepare Could Not Be Completed. Statement(s) Could Not Be
Prepared. Line 3 Incorrect Syntax Near "(".

Is this a syntax error I just can't see or am I taking the wrong
approach? Any help would be appreciated.

Your approach is wrong.

Do this way:

1.Create stored procedure with output parameter say "@ContinueProcessing"
2.Write EXISTS (SELECT * from tempFile) inside stored procedure.
3 return value of @ContinueProcessing from procedure as per your logic.
4.Call stored procedure from Execute SQL task like
Exec StoredProcedureName ?

Where "?" is your GlobalVariable - ContinueProcessing.

--
Regards
Bharat Jariwala



"rowan" wrote:

Thanks for your response. What I ended up doing which worked very well
was changing the global variable type to integer and changing the code
in the Execute SQL task to

SELECT count(*) as cnt from tempFile

Then I clicked on Parameters in the Execute task, selected the Output
parameters tab, selected the option Row Value, and then set the
Parameter cnt to my Global Variable ContinueProcessing. Then in the
workflow I changed my if statement from "if
GlobalVariables("ContinueProcessing") = true" to "if
GlobalVariables("ContinueProcessing") > 0"
DTSStepScriptRewsultExecuteTask else
DTSStepScriptResult_DontExecuteTask. It worked like a charm.