Problem with my First DTS Transformation Package

[ltgrady]

Hey All, doing my first DTS Package using the Data Transformation Service Local Package. I am having a problem with one of my "Transform Data Tasks". This is a SQL to SQL data conversion.

I am trying to copy from one table to another using a "Transform Data Task". I am using an ActiveX Script transformation.

The Source table has several features. Feat1, Feat2, Feat3, etc., in a single row. The new database has unlimited features so we created a table that hold Feat_ID and Feat_Desc. So If there are three features in a record, we now have three records in the new table. Here is my code:

Function Main()
x=0
IF NOT IsNull(DTSSource("Prd_D_Feat1&quot) THEN
DTSDestination("Prd_Model&quot=DTSSource("Prd_Model&quot
DTSDestination("Brand_ID&quot=DTSSource("Brand_ID&quot
DTSDestination("Prd_Feat_Feature&quot=DTSSource("Prd_D_Feat1&quot
DTSDestination("Prd_Feat_ID&quot=1
Main = DTSStepScriptResult_ExecuteTask
x=1
END IF
IF NOT IsNull(DTSSource("Prd_D_Feat2&quot) THEN
DTSDestination("Prd_Model&quot=DTSSource("Prd_Model&quot
DTSDestination("Brand_ID&quot=DTSSource("Brand_ID&quot
DTSDestination("Prd_Feat_Feature&quot=DTSSource("Prd_D_Feat2&quot
DTSDestination("Prd_Feat_ID&quot=2
Main = DTSStepScriptResult_ExecuteTask
x=1
END IF

IF x=0 THEN Main=DTSTransformStat_SkipRow ELSE Main=DTSTransformStat_Ok


There would actually be 18 of these blocks instead of 2, I am just trying to work out the code first (there are 18 feature fields per row). It is executing but it is only putting one record into the new table. If there are two features I get the second one in my new table, if there is only one feature I get the first feature in my new table. If there are no features I get no record, that is the only part that is working.

I am sure I am not executing these blocks right, but each one should be creating a record in the new table.

i would love some help, thank you very much in advance