SSIS OutputColumnCollection Index

Question

SSIS OutputColumnCollection Index

zbleslav

SSC Enthusiast

Points: 170
More actions
March 9, 2010 at 5:29 am

#137588

Dear All,
I would like to make following asynchronous transformation script component more "universal".
There is one Input column: TextCol
Output columns are : c001, c002, c003
Example Input data (name, age, town) - 9 rows
Ron
23
London
Jack
26
Zagreb
Mike
33
New York
Output should be - 3 rows:
Ron23 London
Jack26Zagreb
Mike33New York
Following code works ok:
Public count As Integer = 1
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If count = 1 Then
Output0Buffer.AddRow()
End If
Select Case count
Case 1
Output0Buffer.c001 = Row.Textcol
Case 2
Output0Buffer.c002 = Row.Textcol
Case 3
Output0Buffer.c003 = Row.Textcol
count = 0
End Select
count = count + 1
End Sub
But, instead of using names of the columns (Output0Buffer.c001 = ) I would like to use some kind of index Output0Buffer(Index).Value or something like that.
I would appreciate your comments suggestions 🙂

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I have done something similar in the past only on the input collection. The problem was and is that there is no enumeration of the column list available directly in the script component. I found some code that I used as a basis, I would have to look for it to show you, but it used .net reflection and was SLOW, REALLY SLOW. You can do it with a custom component fairly easily but you have to ask yourself how much that universality is worth to you.

CEWII

zbleslav SSC Enthusiast Points: 170 More actions · Answer 2

Elliott W (3/9/2010)
I have done something similar in the past only on the input collection. The problem was and is that there is no enumeration of the column list available directly in the script component. I found some code that I used as a basis, I would have to look for it to show you, but it used .net reflection and was SLOW, REALLY SLOW. You can do it with a custom component fairly easily but you have to ask yourself how much that universality is worth to you.
CEWII

Thank You Elliot for answer.

Fact is that I have 2 xml files from SAP (17 and 94 rows to "pivot" or "transpose") and in the future will be more. Making code with endless "case select" have no sense to me. Do you have any suggestion how to make transpose before loading into destination?

Thanks again.

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

Have you tried to use standard Pivot/Unpivot transformations?

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