• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9066
  • Last Modified:

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
0
rmtogether
Asked:
rmtogether
  • 3
  • 2
  • 2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Reza RadCommented:
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 RadCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now