Hi!

I have this code in the trasformation tab of my Data Driven Query Task.
What the does task does is it reads a flat file and then adds any new records it finds to the SQL server database. The flat file can contain records that are already in the database. I don't intend to do anything on those, I just need the new ones.

The problem is when I first execute the DTS with an empty destination table, it works. But when I ran it again the second time around against a different flat file(it should insert at least 300 new records from it), it gives me an error message.

ITN2 is the primary key.

Here is the transformation code:

Function Main()
strMth = MonthName(month(date))
Select Case strMth
Case "January"
mth = 1
break
Case "February"
mth = 1
break
Case "March"
mth = 1
break
Case "April"
mth = 1
break
Case "May"
mth = 1
break
Case "June"
mth = 1
break
Case "July"
mth = 1
break
Case "August"
mth = 2
break
Case "September"
mth = 2
break
Case "October"
mth = 2
break
Case "November"
mth = 2
break
Case "December"
mth = 2
End Select

DTSDestination("MID") = DTSSource("Col001")
DTSDestination("IIN") = mth & DTSSource("Col003")
DTSDestination("ITN2") = DTSSource("Col004")
DTSDestination("ITN") = DTSSource("Col005")
DTSDestination("ITN3") = DTSSource("Col006")
DTSDestination("GENDER") = DTSSource("Col007")
DTSDestination("LANG") = DTSSource("Col008")
DTSDestination("TITLE") = DTSSource("Col009")
DTSDestination("NAME") = DTSSource("Col010")
DTSDestination("ADDRESS") = DTSSource("Col011")
DTSDestination("APT") = DTSSource("Col012")
DTSDestination("CITY") = DTSSource("Col013")
DTSDestination("STREAM") = DTSSource("Col014")
DTSDestination("PROV") = DTSSource("Col015")
DTSDestination("POSTCODE") = DTSSource("Col016")

If DTSLookups("Count_ITN2").Execute (DTSSource("Col004")) = 0
Else
Main = DTSTransformstat_InsertQuery
End If
End Function


Here's the lookup code:

SELECT COUNT(*) AS Expr1
FROM [Mailout DB]
WHERE (ITN2 = ?)


Here's the error message:

Active Script Transform Server 'DTS_Transformation__1': script function 'Main' returned an invalid DSTTransformStatus value, or an ActiveX Script did not return an integral type. No further work will be done by the DataPump.

Thanks.