Is it possible to retrieve data from a specific row in an ado recordset in SSIS 2005?

Question

Is it possible to retrieve data from a specific row in an ado recordset in SSIS 2005?

dndaughtery

SSChampion

Points: 11290
More actions
January 12, 2011 at 3:31 pm

#232052

I have a recordset which has a unique id int column. I want to be able to create another recordset based off of the value in one of the columns in the first record. Is there any way to do this without looping through the whole recordset?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

dndaughtery (1/12/2011)
I have a recordset which has a unique id int column. I want to be able to create another recordset based off of the value in one of the columns in the first record. Is there any way to do this without looping through the whole recordset?

No, there is no way.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Hello,

If I'm understanding your question correctly, this actually can be done. First a question,

Are you using a recordset destination and saving the data to a SSIS object variable? If this is what you are doing, then yes this can be done with a script component and a few lines of .NET code. It does take a little bit of work to set this up:

1) In your first dataflow task, get your data from your source and save it to a recordset destination using a Object variable.

2) In your second dataflow task (after completion of the first) add a script component as a source.

3) Use .NET's OleDBDataAdapter and datatable objects to fill the datatable from the SSIS object variable you created in prior dataflow task. Filter the datatable to what you need using a dataView.

4) Loop through dataView saving each column to the script component's corresponding column.

In order to do this, you must create output columns in the script component that coorespond to the columns in your datatable and use the AddRow method of the script component to add each row. You then set the script component's column equal to the dataview's column

A few more steps and a little more work, but it's doable.

Thanks,

Strick

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Hello,

Another thing I just thought of: Where was the data prior to getting it in a recordset? Reason I ask is because you may also be able to achieve this without looping by just using a conditional split task to filter to what you want.

Strick

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

stricknyn (1/18/2011)
Hello,
Another thing I just thought of: Where was the data prior to getting it in a recordset? Reason I ask is because you may also be able to achieve this without looping by just using a conditional split task to filter to what you want.
Strick

Strick,

Read carefully the original question. Both of your messages doesn't answer the question.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/