Call AddRow() After All ProcessInputRow() Are Called In A Script Component

Hi Guys,

I am new to SSIS. Heree is the transformation I need to do. In database one, I have tables:

A

B

C

Where B is a detailed table of A, and C is a "derived" table of B. There is a one to one relationship between B and C, but there may be more than one record in B for each record in A.

In database two, we have the table structure:

AA

CC



And there is a one to one relationship between AA and CC. And I need to design a transformation to migrate data from database one to database two.

Table A(->AA) and B(->BB) will be easy, just one to one migration.

The mapping rule for table C(->CC) they decided was: I need to concate each record in B and C and for a record in CC. For example, suppose we have:

A1

B1(C1)

B2(C2)

A2

B3(C3)

B4(C4)

B5(C5)

Then we will have the following records in database two:

A_1

(B1+C1+B2+C2)

A2

(B3+C3 + B4+C4 + B5+C5)

I looked through all the stock data flow components, and it seems to me that none of them can perform this task, so I am thinking to design a Script transform component to do the task.

I have written the script:

in each ProcessInputRow() sub, I check record's foreign key to A, and if they are the same I concate the records, then I put them into a VB.NET collection. Once all the records are processed, in the PostExecute() function, I count the number of new rows, (in the above example 2), then I call AddRow to add the rows, by:

OutputBuffer.AddRow()

But this does not work, I got "Object reference not set to an instance of an object". It seems that in PostExecute, the OutputBuffer is not longer valid?

Help please! :)

Is there a better way of doing what I am trying to do?

Thanks!

Wenbiao