SSIS Getting Execute Sql Task result set object

Question

I have an execute sql task item and it is getting multiple rows of data from a stored proc.

Declared a variable ObjShipment under the variable table and assigned it under the Result set with the following info:

Result Set:    Full result set
Result Name:   0
Variable Name: User::ObjShipment

I have written a script task with the ObjShipment variable assigned to ReadOnly an am wondering how to retrieve the data within it?

The stored proc returns multiple rows like Id, ItemId, DateCreated.., but how do I retrieve them say if I am only interested in the ItemId? And since it returns multiple rows there could be more than one ItemId.

I am new with ssis any help would be appreciated!

Answer 1

So generally speaking you use Object variables within an SSIS package as the Enumerator of a For Each container.

1. Create a For Each Loop Container.
2. Set the Enumerator to a "For Each ADO Enumerator".
3. Set the source variable to User::ObjShipment.
4. Assign each column from your object to its own variable in the Variable Mappings tab.
5. Within the For Each Loop container, use those variables to do whatever you want: insert them into a database, do lookups and audits, etc.

If you are going to use a Script Task, then you all need to do is

DataTable dt = new DataTable();
OleDbDataAdapter oleDa = new OleDbDataAdapter();
oleDa.Fill(dt, Dts.Variables["User::objShipment"].Value);

And then use dt like any old DataTable.