Sending result set to variables for use in subsequent step

Question

Sending result set to variables for use in subsequent step

Rich Hansell

SSCrazy

Points: 2346
More actions
November 10, 2006 at 2:22 pm

#115075

HI guys/gals I have been spinning my wheels here for a bit..
Here is my issue:
I have one step that runs a query
select A, 98 as B,Getdate() as C
from Table
where Attribute = 1
and Attribute =1
Now I set the resultset to "Full Result Set"
and mapped each to a variable in the package - note I am expecting multiple rows.
Result name , variablename
0 , Variable1
1 , Variable2
2 , Variable3
When I run this step I get this failure
Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Any ideas anyone?
November 13, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Holly Kilpatrick Ten Centuries Points: 1195 More actions · Answer 1

This answer may not address your situation exactly, but the situations where I have used FullResultSet I have mapped it to a variable of type object, based on the example in Brian Knight's video

http://www.jumpstarttv.com/Media.aspx?vid=38

using the Foreach container with a Foreach ADO enumerator to do my task with each row of the result set. In the cases where I have used it, however, I have been setting the results one after the other to the same string variable inside the Foreach container. So to make this approach work you would have to add another step to identify which variable to use in each case.

Holly

Rich Hansell SSCrazy Points: 2346 More actions · Answer 2

Thanks Holly.. I think I found the the solution.. in a dataflow task I added an OLEDB source and funneled the results into an OLEDB command task and just mapped the results to the parameters of the query I was running. Thanks for your time though!

anatre SSC Rookie Points: 25 More actions · Answer 3

can you add an example of what you did ?

a zip file of this in package ?

Chinna SSCertifiable Points: 5408 More actions · Answer 4

hi holly can u please let us know how did u do that bcoz i couldnt get u ...if poss with a n example...thkx

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

tamer yousef Valued Member Points: 71 More actions · Answer 5

sorry for bringing this back, but I wanted to send the answer for anyone who may be interested:

There are two cases:

case1:

when you return a single row from a table: for example: you have an employee id and want to return some info about this employee in a variable: in that case, in the Execute SQL Task Editor ---> General --> Result Set : set it to "Single Row".

and then go to the result set and map the single row output to your variables output.

Case 2:

when you have more than 1 record, that when you should select Full Result set, and then set the output to a variable of type OBJECT. then use this object as the input for a ForEach Loop, and do the mapping there similar to step1.

sorry for making this short but I hope this make sense.

-Tamer

bkirk SSCrazy Points: 2824 More actions · Answer 6

What about an XML resultset type?

I have a rather complex SQL statement which uses FOR XML PATH to produce an RSS XML Output. I am then trying to pass the output variable (string) to a Script task that streams the string output to an XML file.

I am still getting errors trying to output the XML resultset. Any tips?