Avatar of rmtogether
rmtogether asked on

SSIS reuse the object variable

Hi, Experts,

I am working on a SSIS project in SQL server 2008

I have a problem to reuse object variable inside a [foreach loop]. But other variables like string or int works ok inside the  [foreach loop].

I describe my procedure in the attached picture. and my the code that I used to call object in is in the attached code.

(1) I generate a a recordset and want ti to be used everywhere in my package. I generate the recordset from a [control flow task] and then

(2) My problem is when in the [Foreach loop], in 1st time I can get correct values from object variable , but in later time it seems object variable is empty (please see the attached picture)

Please help me this. thanks in advance.





public override void CreateNewOutputRows()
    {

        //[Obj:location]
        OleDbDataAdapter DA_Loc = new OleDbDataAdapter();
        DataTable DT_Loc = new DataTable();
        DA_Loc.Fill(DT_Loc, this.Variables.objLoc);
        MessageBox.Show(DT_Loc.Rows.Count.ToString()); // count row number
}

Open in new window

Snap1.jpg
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Reza Rad

8/22/2022 - Mon
brad2575

The first time it works because you are looping through it, the second time it appears empty because you are at the end of the record set, you would need to do something to start/reset the record set to start from the beginning again.
ASKER
rmtogether

Thank you

could you please teach me how can I reset the record set to let it start from the beginning?
ASKER CERTIFIED SOLUTION
brad2575

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Reza Rad

I don't understand why you don't used result set of your object variable in for each loop?

do you want to loop through your variable in foreach loop?
this is the way:
-set your for each loop with ado.net enumerator, and set variable as source
- in result set tab, set columns of results to other variables.
- use other variables in Tasks inside the foreach loop.


Am I get your problem correctly? if not explain more.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
rmtogether

Hi,  reza_rad

I do use the recordset object in [Foreach Loop]. Since I have different result set. The one I need repeat is the location recordset.

So in my case when ever a loop run, I need use (copy) the data from that location recordset
ASKER
rmtogether

Hi, brad2575


You say "take your recordset and push it into an ADO.Net DataTable". Yes I think I need use DataTable, but is it possible to save the result directly as a DataTable ?

Anyway, I will try to read through it.
Reza Rad

unfortunately , I didn't get your problem clearly yet !
could you explain an step by step of what are you doing in the package totally?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.