Result Set in Execute SQL Task

Question

Post reply

Result Set in Execute SQL Task

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
September 7, 2011 at 6:18 pm

#247595

I have declared a variable and capturing that variable through Result Set in Execute SQL Task. Now, I need to accesss that variable in Dataflow task. But I am getting error like "Must Declare the variable". Does this mean I cannot access to that variable in DataFlow task? BTW, the scope of the variable is global throughout the package.
Any help would be appreicated.

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

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

dndaughtery SSChampion Points: 11290 More actions · Answer 1

On your Exe SQL task under the gen tab are you setting the result set as single row?

Also, In the EXE SQL task under the ResultSet Tab, are you changing the name of the "ResultName" to 0 (zero)?

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 2

Ensure that you have declared the variable at the correct scope...not just within the ExecuteSQL task, but at the package level.

Hope that this helps.

Thanks,

Chris

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

Also ensure that the variable is of type 'Object' if you are capturing more than one row of data.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 4

Yes I have done all that. Seems like you need to grab that variable through Expression and use SQL command from Variable in Data Flow task. But I would like to get it working the I was doing if that is all possible. Let me know if the variable in Execute SQL Task can be accessed at Data Flow Task.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

If you mean: "can I consume the contents of an object variable as a data flow datasource?" I believe that the answer is no.

There will be an alternative - you might have to go into more detail about what you are trying to achieve before we can help you find it though.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kramaswamy SSCoach Points: 18181 More actions · Answer 6

You might be able to pull it off if you used a Script Task as your Data Source, fed the object variable into the Script Task, and then parsed it into a set of outputs.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

kramaswamy (9/9/2011)
You might be able to pull it off if you used a Script Task as your Data Source, fed the object variable into the Script Task, and then parsed it into a set of outputs.

Script Component, I think you mean. Yes, I believe that will do it, though it won't be blindingly fast. There might be a better/easier way using standard dataflow components.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kramaswamy SSCoach Points: 18181 More actions · Answer 8

Yeah t'was what I meant, my bad.

I wonder - if his data source isn't terribly large, might be easier to just use a FOREACH Loop, with the ADO collection, to loop through the rows?