Hi,
I have a program in C# that creates an DTS package, and executes it. That
DTS takes as an input a comma delimited text file and writes the data into a
SQL table. I am using for that a DataPumpTask, with 1 VBScript
transformation.
I am using the VBScript transformation because I want to be able to check
for errors, notify the error sink, and skip the line . From the documentation
I've read, returning DTSTransformStat_SkipRowInfo would be the best way to
achieve this. Regretably, the error sink doesn't get called. If I return
something else (that will throw an error), the error sink gets called, but
regardless of the value of the pbCancel variable I set in there, the package
will fail (throw an exception).
package.FailOnError is not of any use either, because if thats false, the
package will just terminate without throwing an exception, if its true it
will throw an exception.
Now, I have alternatives to this, like for example writing into a file the
rows that fail the tests, return DTSTransformStat_SkipRow, and after that
read that file. But its a very messy solution, there must be a better way of
handling this.
Am I wrong?

I am using the proper way (as recomended on MSDN) to register for events
notification, not through the event handlers that C# Package class provides
but using the UCOMIConnectionPointContainer and a PackageEventSink class.


the DTS code (as generated by my program) looks like this:
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************
' Copy each source column to the destination column
' Doing the following checks:
' 1.Make sure that every variable is of the required type (a date is a
date, an int is an int,etc.)
' 2.The variables don't exceed SQL Server limits
Function Main()
ValidationOK=True
If IsDate(DTSSource("Col002")) Then
InYear = Year(DTSSource("Col002"))
If InYear<1900 OR InYear>2079 Then
ValidationOK=False
End If
Else
ValidationOK=False
End If
If ValidationOK Then
DTSDestination("fkMetStationID")=DTSSource("Col001 ")
End If
If ValidationOK Then
DTSDestination("hourlyDate")=DTSSource("Col002")
End If
If ValidationOK Then
DTSDestination("windDirection")=DTSSource("Col003" )
End If
If ValidationOK Then
DTSDestination("windSpeed")=DTSSource("Col004")
End If
If ValidationOK Then
DTSDestination("windDirectionID")=DTSSource("Col00 5")
End If
If ValidationOK Then
DTSDestination("IsApproved")=DTSSource("Col006")
End If
If ValidationOK=False Then
Main = DTSTransformStat_SkipRowInfo
Else
Main = DTSTransformStat_OK
End If
End Function



I am using VS.Net 2003 (.NET framework v1.1.4322), on Win2000 SP4 with all
the updates applied.
SQL Server 2004 with SP4 applied to it (both on the client and the server).

Thank you very much for any information provided.
Sergiu.