I have this script in my datapump task ,I'd like to update a field in the
current row when an error happens ,,

Function Main()
IF DTSSource("Col001") <> "C1" THEN
DTSDestination("ERROR_STATUS")= 8888
Main = DTSTransformStat_SkipInsert
END IF

DTSDestination("RECORD_TYPE") = DTSSource("Col001")
DTSDestination("ERROR_STATUS") = 55
Main = DTSTransformStat_OK

End Function

The problem of this script is that ,all fields (even those are not equal to
C1 ) are set to 55 and there is no 8888

Thanks
RoyAll

You are asking to skip the insert if col001 = "C1". That would explain
missing values

Do you not want something like

IF DTSSource("Col001") <> "C1" THEN
DTSDestination("ERROR_STATUS")= 8888
END IF
DTSDestination("RECORD_TYPE") = DTSSource("Col001")
DTSDestination("ERROR_STATUS") = 55
Main = DTSTransformStat_OK



"ALI-R" <newbie (AT) microsoft (DOT) com> wrote

In fact it should probably look more like

IF DTSSource("Col001") <> "C1" THEN
DTSDestination("ERROR_STATUS")= 8888

ELSE

DTSDestination("ERROR_STATUS") = 55
END IF

DTSDestination("RECORD_TYPE") = DTSSource("Col001")

Main = DTSTransformStat_OK



"Allan Mitchell" wrote:

The trick here is that ,when a validation error happens I'd like to update a
column in the row and skip processing other columns in the row and move to
the next row.

Thanks for yuor reply
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

This is for Inserts right?

So providing you do not find a problem you insert to the destination
If you do find a problem then you update the same row you are processing
on the Source to something?

You may get locking contention


You could also log the Sources on the Source with which you have the
problems to a log table by using a Lookup that does an INSERT

You could also use the Source provider's syntax to filter out rows that
would cause you to not insert a row leaving you with only good rows and
then use another task that does the UPDATES after using a good WHERE
clause.




"RayAll" <RayAll (AT) microsft (DOT) com> wrote

I update the same row in the destination not source.
Thanks
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The datapump is for inserts. Depending on batch sizes you cannot really
do an UPDATE because the row does not really exist.

Is this the same Q as your validation question using Global Variables?



"ALI-R" <Ray (AT) Alirezaei (DOT) com> wrote

yes,they are some how related.I'll follow it up there (in my other posting)

Thanks for monitoring my threads

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote