execute sql task resultset and variable

Question

execute sql task resultset and variable

keywestfl9

SSCertifiable

Points: 5215
More actions
February 25, 2010 at 4:43 pm

#156717

i have a package where i pass accountnumber to an execute sql task inside a for each loop and store the result in a package level variable.
i am passing the accountnumber as input parameter to execute sql task
i want to know how to extract the values from the resultset variable?
please suggest

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

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

Since the values of a result set are normally stored in a object they cannot be retrieved .. Alternative is to use a script component and convert this object variable to string and assign this to a string variable.. Is that what you are looking for?

dim s as string

s= Dts.Variables("objvariable").Value.ToString

Stringvariable = s

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

You can use a script task to do it, here is the basis for the code:

Code to walk through OLEDB data..

Dim _oOledbAdapter As New OleDb.OleDbDataAdapter

Dim _oDataTable As New System.Data.DataTable

Dim _oDataRow As Data.DataRow

_oOledbAdapter.Fill(_oDataTable, Dts.Variables("ResultSetVariable").Value)

For Each _oDataRow In _oDataTable.Rows

'Walk through fields for this row here using _oDataRow.Item(1).ToString

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

keywestfl9 (2/25/2010)
i have a package where i pass accountnumber to an execute sql task inside a for each loop and store the result in a package level variable.
i am passing the accountnumber as input parameter to execute sql task
i want to know how to extract the values from the resultset variable?
please suggest

What does the resultset look like: 1 value, 1 record or multiple records. In case of 1 or more records, what are the columns?

What is your loop doing?

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 4

this package contains two execute sql task..

1. to start with i find out the duplicate records and pass to the other execute sql task which is in the for each loop

second execute sql task which is in for reach loop will extract data pertaining to duplicate records by the sql statement i have in the sql task and the resultset i store it in a variable (user::finalresult)

i want to retrieve the values of the variable and create a txt file or excel and attach it in a email.

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 5

I keep getting this error ..i tried this option mentioned

dim s as string

s= Dts.Variables("objvariable").Value.ToString

Stringvariable = s

The element cannot be found in a collection. This error happens when you try to retrieve an element

from a collection on a container during execution of the package and the element is not there.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Do you have "objvariable" in the readonly or readwrite variables?

CEWII

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 7

you need to add "objvariable" as readonly and the other variable as writeonly and then use them in the script task... You can add them in the general tab of script component/task