Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

GO statement breaks SSIS Execute SQL Task

 
I have lost an hour today trying to find a bug in my SSIS package, and it was so small - just two letters - "GO"

I had an Execute SQL Task with an input parameter and I had an SQLStatement with some lengthy logc and a "GO" at the end.

Every time I tried to execute it I got an error message "[Execute SQL Task] Error: Executing the query "
" failed with the following error: "The variable name '@StartFrom' has already been declared. Variable names must be unique within a query batch or stored procedure."

I got desperate trying to find where am I declaring the @StartFrom. I even removed it completely from script and still got this error. Eventually, gloomily poring over my script I saw a "GO" statement and decided that maybe he is the culprit - causing Execute SQL Task to declare the Input Parameter twice, and I was right. That is exactly what happens.

When Execute SQL Task sees GO, it redeclares the variable specified in the Parameter Mapping, and then himself not liking it complains about it being declared twice. Isn't that just ingenious?

Comments

Posted by Brett Flippin on 30 November 2011

Should "GO" ever be in SSIS Execute SQL Task anyway? If you have separate queries to execute it would be more logical to break those into separate Execute SQL Tasks. Both for maintenance and auditing purposes.

It's good to know how it works though, but I'd argue that it shouldn't be there in the first place.

Posted by Anonymous on 1 December 2011

Pingback from  Dew Drop – December 1, 2011 | Alvin Ashcraft's Morning Dew

Posted by aedna on 1 December 2011

wel, GO could be there because of simple copy-paste when you are writing your script in the Query Analyzer first, using tempaltes...

or GO could be needed for statmenets that create schemas for example - they need to be in a spearate batch....

of course there is always a workaround. it's just this bug is such a hard-to-finder :)

Leave a Comment

Please register or log in to leave a comment.