?
Solved

SSIS reuse the object variable

Posted on 2010-05-26
7
Medium Priority
?
8,518 Views
Last Modified: 2013-11-10
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
Comment
Question by:rmtogether
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 32852597
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
 

Author Comment

by:rmtogether
ID: 32852823
Thank you

could you please teach me how can I reset the record set to let it start from the beginning?
0
 
LVL 16

Accepted Solution

by:
brad2575 earned 2000 total points
ID: 32852989
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 30

Expert Comment

by:Reza Rad
ID: 32855711
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
 

Author Comment

by:rmtogether
ID: 32856424
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
 

Author Comment

by:rmtogether
ID: 32856441
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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 32858599
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question