How to Export Full Result Set Variable

Question

How to Export Full Result Set Variable

lorisj33

SSC Eights!

Points: 940
More actions
April 7, 2016 at 1:44 pm

#326317

Hello all,
I'm hoping someone can set me straight. I'm trying to use an Execute SQL Task, which seems successful, using an OLE DB connection, File Connection SQL Source Type, with Result set Variable Object with Result Name = 0. My Object result set variable is called User::ResultSet3, and how do I export the contents to a destination flat file. It seems like it should be easy enough, but I'm scratching my head.
I've found info online to use a script task (VB or C++) to export the system.data.dataset object, but I believe since the connection manager is OLE DB is is considered a Recordset. So how do I get the object variable recordset exported to a flat file destination?
Thanks for any help you could provide!! (I'm not a 'programmer.')
- Lori

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246873 More actions · Answer 1

lorisj33 (4/7/2016)
Hello all,
I'm hoping someone can set me straight. I'm trying to use an Execute SQL Task, which seems successful, using an OLE DB connection, File Connection SQL Source Type, with Result set Variable Object with Result Name = 0. My Object result set variable is called User::ResultSet3, and how do I export the contents to a destination flat file. It seems like it should be easy enough, but I'm scratching my head.
I've found info online to use a script task (VB or C++) to export the system.data.dataset object, but I believe since the connection manager is OLE DB is is considered a Recordset. So how do I get the object variable recordset exported to a flat file destination?
Thanks for any help you could provide!! (I'm not a 'programmer.')
- Lori

For this, you should use a data flow task, not an execute SQL task.

Define source (your query) and target (your file) within the data flow.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

lorisj33 SSC Eights! Points: 940 More actions · Answer 2

Thanks Phil,

I'm testing it this way because I eventually want to wrap it in a for each file loop, reading each sql file and exe the code one by one, and outputing the result set (object variable) of multiple rows and columns into a destination flat file.

I can do it easily with your method, but that doesn't get me to my ultimate destination. So I'm back to how do I output the contents of the object variable? I can't seem to use a variable as a source in any of the SSIS tasks.

Thanks,

Lori

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

lorisj33 (4/8/2016)
Thanks Phil,
I'm testing it this way because I eventually want to wrap it in a for each file loop, reading each sql file and exe the code one by one, and outputing the result set (object variable) of multiple rows and columns into a destination flat file.
I can do it easily with your method, but that doesn't get me to my ultimate destination. So I'm back to how do I output the contents of the object variable? I can't seem to use a variable as a source in any of the SSIS tasks.
Thanks,
Lori

This feels like you may be over-engineering the problem. And I am worried about performance too.

Why not put a script task in your FEL and execute the SQL in that, firing the results out to file directly instead of messing about with object variables?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

lorisj33 SSC Eights! Points: 940 More actions · Answer 4

Thanks Phil, let me try that. I'll let you know how I make out.