SYMPTOMS
When you try to use ActiveX transformations with Microsoft SQL Server Data Transformation Services (DTS) where the source column is a numeric data type, the following error message can occur when you perform mathematical operations:
Error Code: 0
Error Source: Microsoft VBScript runtime error
Error Description: Type mismatch
WORKAROUND
To work around this problem, use the
Cast function to convert the unsupported data type into a data type that a script written in VBScript can support.
You can use the following T-SQL script and VBScript to demonstrate the behavior:
CREATE TABLE dtstest (
Rate1 numeric(27,23),
Rate2 numeric(27,23)
)
go
INSERT INTO dtstest VALUES (0.081625, 0.081625)
go
Create a DTS transformation between dtstest and a new table. In the transformation, select VBScript ActiveX transformation, and the use the following code snippet to perform the transformation:
Function Main()
Dim Rate
REM The following statement allows VBScript to perform the
REM expression against a numeric data type.
REM Rate = CDbl(DTSSource("Rate1")) / CDbl(DTSSource("Rate2"))
REM The following statement causes the error because VBScript
REM is not able to handle the numeric data type. Replace the
REM following statement with the preceding statement.
Rate = DTSSource("Rate1") / DTSSource("Rate2")
DTSDestination("Rate1") = Rate
DTSDestination("Rate2") = DTSSource("Rate2")
Main = DTSTransformStat_OK
End Function