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.
Error converting data type varchar to numeric.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 35
- Joined: Tue Feb 13, 2007 5:30 am
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
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
Re: Error converting data type varchar to numeric.
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)
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)