I am working on a SQL Server data transformation to refresh our new
..NET project's database with our old ASP project's database. In the
old database we had 18 fields in our Product table for 18 different
features of a product. In the new database we have a seperate
features table, one record per feature, one to many relationship with
product table. I am trying to loop through the old Products table in
a Transform Data Task and insert one record per feature into the new
features table.

After my job is finished running, it is only pulling that last
feature. If there are 8 features it only inserts #8, if 18 it inserts
only that last one. There is a command I need before each END IF
statement that will insert the record and move to a new one for hte
next IF clause. Can anyone help me with this? Thanks.

Here is my code:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
x=0
IF NOT IsNull(DTSSource("Prd_D_Feat1")) THEN
DTSDestination("Prd_Model")=DTSSource("Prd_Model")
DTSDestination("Brand_ID")=DTSSource("Brand_ID")
DTSDestination("Prd_Feat_Feature")=DTSSource("Prd_ D_Feat1")
DTSDestination("Prd_Feat_ID")=1
Main = DTSStepScriptResult_ExecuteTask
x=1
END IF
IF NOT IsNull(DTSSource("Prd_D_Feat2")) THEN
DTSDestination("Prd_Model")=DTSSource("Prd_Model")
DTSDestination("Brand_ID")=DTSSource("Brand_ID")
DTSDestination("Prd_Feat_Feature")=DTSSource("Prd_ D_Feat2")
DTSDestination("Prd_Feat_ID")=2
Main = DTSStepScriptResult_ExecuteTask
x=1
END IF

****
<--- THE SAME CODE FOR ALL 18 Features, DELETED TO SAVE SPACE --->
****

IF NOT IsNull(DTSSource("Prd_D_Feat18")) THEN
DTSDestination("Prd_Model")=DTSSource("Prd_Model")
DTSDestination("Brand_ID")=DTSSource("Brand_ID")
DTSDestination("Prd_Feat_Feature")=DTSSource("Prd_ D_Feat18")
DTSDestination("Prd_Feat_ID")=18
Main = DTSStepScriptResult_ExecuteTask
x=1
END IF

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

End Function

You need to process each source row 18 times, to get the 18 inserts, then
move on. Lookup DTSTransformStat_SkipFetch in Books Online. Here is an
example as well-

Processing The Same Row More Than Once
(http://www.sqldts.com/default.aspx?266)


--
Darren Green
http://www.sqldts.com

"Larry" <google1 (AT) larrygrady (DOT) com> wrote

I think the only solution is to pack the database first. SQL doesn't seem to
understand what these are and how to treat them.


--
Darren Green
http://www.sqldts.com

"Larry" <google1 (AT) larrygrady (DOT) com> wrote

Oops, wrong thread.

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote