ForEachLoop and Object-Variable

Hi there!

I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).

So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).

The variable-mapping looks like that:

Mypackvar1 - Index 0

Mypackvar2 - Index 1

Mypackvar3 - Index 2

Mypackvar4 - Index 3

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Am i on the right way? Is it great bullshit what i am doing?

Thanks for any suggestion,

Torsten

Sounds like you're on the right track. What I do is create an ExecuteSQL task with the result set set to "Full result set". In the Result Set page I click Add, put 0 for the result name and pick an Object variable to put the result in.

In the For Each loop I make the collection a "Foreach ADO.Net Schema rowset enumerator".
In variable mappings I pick a variable with the same type as the column and put in the appropriate offset ( 0 through fieldcount-1).

It sounds like you're doing that or something very close. I'd double check the variable you're assigning to is the same type as the resultset column.

|||

Torsten_Katthoefer wrote:

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Have you stepped through with the debugger to make sure you're getting back the values you expect? Are you calling a stored procedure, or just executing SQL? How are you populating the recordset?

|||

Hmm, it works - a little bit...

One problem has been the datatype - in the db, the column is bigint, and the conversion to DTI8 makes some trouble, so i decided to use a an object as datatype (package scope), and first in the for-each-loop i started a script task like that (CRQ_ID is the variable with type DTI8, and CRQ_OBJ is the result from my query to set the enumerations):

Dim Message As String

Dts.Variables("v_CRQ_ID").Value = CType(Dts.Variables("v_CRQ_OBJ").Value, Int64)

Message = CStr(Dts.Variables("v_CRQ_OBJ").Value) + "-" + CStr(Dts.Variables("v_CRQ_ID").Value) + "-" + CStr(Dts.Variables("v_LGE").Value) + "-" + CStr(Dts.Variables("v_DDS").Value) + "-" + CStr(Dts.Variables("v_FIS_PERIODE").Value)

MsgBox(Message)

I get the message boxes a view times, and everytime CRQ_OBJ is equal CRQ_ID. Seems it works fine.

BUT: The next task is a sql task witch updates a few rows with the CRQ_ID as input parameter. But it doesn't matter on the new values. Could it be, that i've to use another way to set the package variable?