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
rmtogetherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
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.
0
rmtogetherAuthor Commented:
Thank you

could you please teach me how can I reset the record set to let it start from the beginning?
0
brad2575Commented:
Found this on a post online:


Ok, I am posting my solution here because this is the first forum I looked at when I started looking.

As it turns out you cannot reuse recordsets stuffed into SSIS object variables across multiple tasks.
There is a solution though:

This code with take your recordset and push it into an ADO.Net DataTable. From there you can do what you want with it.

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["ExcelSet"].Value);

For some reason this empties your object variable. What???

Now what you have to do is take the data in your DataTable and push it back into your object variable. BUT, the other tasks (and the above code) are expecting an ADOBD.Recordset. So first you have to convert your datatable to an ADODB.Recordset. The code to do that is in the article linked below. Just copy and paste to your script code (note: code is in C#....may have to convert to VB.Net).

http://www.codeproject.com/KB/database/DataTableToRecordset.aspx

Don't forget to set a reference to Microsoft Active X Objects in your script (Project>Add Reference>COM tab>Microsoft Active X Data Objects 2.X....I used 2.7 but any will work). Add a Using ADODB (or Imports ADODB) to the top of your page.

Restuff your object variable:
Dts.Variables["ExcelSet"].Value = ConvertToRecordset(excelTable);

And you are off. You can then reuse your object variable across SSIS tasks. Whew! Coming from the OO world I was quite dismayed to find this didn't work automatically. And, really Microsoft, an ADODB recordset. How 1999.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Reza RadConsultant, TrainerCommented:
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.
0
rmtogetherAuthor Commented:
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
0
rmtogetherAuthor Commented:
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.
0
Reza RadConsultant, TrainerCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.