SQL 2005 (SSIS) Execute SQL Task Error: he result binding name must be set to zero for full result set and XML results.

I can not find any information on the following error.  I would think this would be easily resolved  Can you please help me resolve

Using SQL 2005's Integration Services,

1) I created an Execute SQL Task which does a simple select:  "select * from test.mytable".  
2) I set the Execute SQL Task's ReturnResult  to "XML"
3) I created a global scoped variable called "RecordSet" and made it type "Object"
4) using the I Execute SQL Task's pane, I mapped "NewResultSet" to "User::Recordset"
5) When I go to run the task, I get the following error:

Error: 0xC00291E3 at Execute SQL Task, Execute SQL Task: The result binding name must be set to zero for full result set and XML results.


How do I map a result set to a variable - what have I done wrong?

vamail2Asked:
Who is Participating?
 
nmcdermaidCommented:
Try changing the name "NewResultSet" to just the number 0
0
 
vamail2Author Commented:
OHMYGOD!  What is with that?    If i wanted to save the result in several variables would I reuse 0?   When would you use "1"?

Thanks!
0
 
vamail2Author Commented:
And how do I use a "full result set" in a script when I retrieve it from a variable like so:

  Dts.TaskResult = Dts.Results.Success
        Dim var As Variables
        Dts.VariableDispenser.LockOneForRead("RecordSet", var)

        Dim o As Object
        o = var("RecordSet")
0
 
nmcdermaidCommented:
I think the idea is that the XML will only ever go into one recordset variable.

I'm not familiar with that piece of script but I know if you got a full resultset it would be the same - the entire recordset goes into one ADO recordset variable.

Then you can use ADO methods to navigate through it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.