Using Return Codes in DTS
In Data Transformation Services (DTS), you can use Microsoft® ActiveX® script return codes to:
- Process row data in a transformation, including the handling of inserts, errors, and exceptions.
- Set up conditions in a transformation script for skipping rows, reusing rows, choosing whether to write the row to the destination, and terminating row processing based on an error.
- Control package workflow or step execution.
- Execute the correct query type for a data driven query.
DTS provides a number of return code constants. Depending on where you use the ActiveX script, these return codes have different effects on the package or rows:
- Return codes used inside an ActiveX Script task are sent to the package, where they control the flow of steps through the use of precedence constraints. The success or failure of one task activates any On Success or On Failure precedence constraints on any tasks that follow it.
- Return codes used in a transformation script or a script that operates on a row-by-row basis apply to the row being processed.
Note Use the return code constants rather than the decimal or hexadecimal values of the transformation return code in the script.
- A return code in a Data Driven Query task defines which SQL query to execute on each row processed.
Using Multiple Return Codes
Some situations require multiple return codes. For example, if you want to transform a row multiple times but do not want to insert it into the destination multiple times, you can use DTSTransformStat_SkipFetch to transform the row again. Or, you can use DTSTransformStat_SkipInsert to prevent it from being put into the destination. However, no return code can do both. In that situation, you can use an OR operator to combine actions. For example, Main = DTSTransformStat_SkipFetch OR DTSTransformStat_SkipInsert causes both actions to occur. DTSTransformStat_SkipFetch prevents another row from coming into the transformation, and DTSTransformStat_SkipInsert keeps the row from being inserted into the destination.