ActiveX script task for import multiple data

[redrick]

I need to import multiple data into a db, but I cannot do it without operating by multiple transformation tasks. I want to do it (if it is possible) with a single transformation. Here's the situation of the source data:
Year Code A B C
1995 100 1 2 3
1996 200 4 5 6
1997 300 7 8 9
... ... . . .
I want to put them into a table that has:
Year Code Data

Each transformation puts the data (into the Data field) for A, B and C (so I've 3 transformation tasks), but I want to do it for all columns in a single transformation task.
Is there a way to put the data in the table for A, then for B and then for C into the same ActiveX script task of a transformation?
I've tried with Main = DTSTransfer_AppendData but it didn't work.
Thank you very much,

Riccardo

 

[tlbroadbent]

There is an article at SQLDTS.com that shold help you do what you need to do.

Processing The Same Row More Than Once

http://www.sqldts.com/default.aspx?6,103,266,0,1

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[redrick]

Right, I've seen it. But the example takes the same column name for source (multiple data) and puts them into the unique destination column (as my case).
But I've source columns with different names (they're identified by an ID such as "P2002", "P2003", P2005" that is not sequential, so I can't use a start number and increase it by each loop used to insert data into the array).
To be more clear:
Year Code P2001 P2002 P2005
1995 100 1 2 3
1996 200 4 5 6
1997 300 7 8 9
I want to put into a table with:
Year, Code, ID, Data

So when I get data from P2001 I've to produce:
1995, 100, P2001, 1
1996, 200, P2001, 4
1997, 300, P2001, 7
Then for P2002:
1995, 100, P2002, 2
1996, 200, P2002, 5
1997, 300, P2002, 8
and so on.
I've done the transformations one by one, but I want to optimize the code by doing multiple rows in the same transformation.