Article ID: 252439 - Last Review: October 31, 2003 - Revision: 3.2

PRB: Use of Math Operations with DTS and Numeric Data Types

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q252439

Expand all | Collapse all

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

Back to the top

CAUSE

Microsoft Visual Basic® Scripting Edition (VBScript) does not support the numeric data type and is unable to handle the mathematical operation of the two operands.

Back to the top

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

Back to the top