Error converting data type varchar to numeric.

[Sridhar Sivakoti]

Hi,

I have designed job with source and targets are SQL Server tables. Source metadata is Varchar and Target metadata is Numeric.


I am getting below error, even I used the StringToDecimal conversion function.
APT_CombinedOperatorController,0: [DataDirect][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

Please help on this.

[bcarlson]

You probably have non-numeric data in the input field. You can scrub the data before conversion or use the IsValid function to determine if the character data can be converted into numeric. If you look up StringToDecimal, the IsValid function is documented on the same page.

If the data is invalid, you could default it some error value. For example, if the output is supposed to be positive, convert invalid values to -1. Or filter the records out.

Hope this helps.

Brad

[krishobby]

If you have Null in the incoming rows then the StringToDecimal conversion fail throwing this error.

You can check for Nulls and empty spaces before converting

If IsNull(inputlink1) then SetNull() else If trim(inputlink1) = '' Then Setnull() else If Isvalid("decimal[10,2])", inputlink1) Then StringtoDecimal(inputlink1)