Gonzo TechNet - How to shred/parse/iterate a recordset in an SSIS Script Task
Assume that in an SSIS package you have an Execute SQL Task that returns a Resultset/Recordset into a variable called objRecordset
(e.g. select * from Orders) ...
and now you want to access that data in a ScriptTask to perform some row by row operations.
Here is the code snippet on how you can do it.
(Plagerized/modified from Nayan Patel's post
Create a global variable called objRS and set its Data Type to object
Create a Execute SQL Task
In the General tab, set the ResultSet option to Full Result Set
In the Result Set tab:
Click the button at the bottom of the page
Set the Result Name to 0 (zero)
Set the Variable name to objRS
Create a Script Task
In the Script tab, set the ReadOnlyVariables property to objRS
Click on the button to open the Script Editor
On the Project Menu of Script IDE,click Add Reference
Scroll through the list of Components, elect System.XML.dll
Click then click to save the Reference
In the VB Code section, add the follwoing IMPORTS statments to the top of the page:
Put the code below into the script task
Close the IDE and then click on to close Script Task Editor DialogBox