SSIS - 'Execute SQL' Task and Record Sets

Question

Hi,

How can I access a 'RecordSet' within a 'Execute SQL' task when using SSIS?

I have looked at the parameter mapping options within the Execute SQL Task Editor and cannot find a type of object to allow me to pass the variable holding my record set to the task.

Answer 1

You can't directly. A recordset is an in memory pointer to a dataset, while an ExecuteSql task accesses data from a database. You can use a foreach container with an executeSql task inside it in a technique I have heard called "Shredding" where you point the recordset to the foreach as the enumerator, set the columns in the recordset to variables for each inside the loop, and pass those individual record values (columns) as parameters to an executeSql task.

Andy Leonard does a good job of describing the use of the ForEach.

Answer 2

I think this is possible. You can pass an entire record set as a parameter to some SQL query executed in Execute SQL task if the type of the variable is object. Not sure if there are any glitches in your particular case or if I got your question all wrong.