SSIS executing Sql task result set object

Question

I have a sql execute element and it gets several rows of data from a stored procedure.

Declared the ObjShipment variable in the variable table and assigned it according to the Result parameter with the following information:

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

I wrote a script task with the ObjShipment variable assigned by ReadOnly, and I wonder how to get the data in it?

The stored proc returns several rows, for example Id, ItemId, DateCreated.. , but how to get them, tell me if I am only interested in ItemId? And since it returns multiple rows, there may be more than one ItemId.

I am new to ssis, any help would be appreciated!

Answer 1

Typically, you use the Object variables in the SSIS package as an Enumerator for each container.

• Create a container for each path.
• Set Enumerator as "for each ADO enumerator."
• Set the source variable User :: ObjShipment.
• Assign each column from your object to its own variable in the Mapping Variables tab.
• In the For Each Loop container, use these variables to perform all the necessary actions: insert them into the database, perform searches and audits, etc.

If you intend to use the Script task, you only need to

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

And then use dt, like any old DataTable.