String Conversion in DTS using CDbl
Posted on 2006-05-17
DTS reads a flat text file, and even though the values read in do NOT have preceeding double quotes, it is throwing a type-mismatch error when the script is run. Originally, the transform simply copied the values as is into the table. Problem now exists that as it reads all the data, it chokes on the numerics. It worked in the past with an older version of some code that placed the values in the text file with leading zeros.
old format: 000000000014.99 or -000000000081.29
new format: 14.99 or -81.29
Here's the format framework
"string value of some sort" <tab> "another string Value" <tab> "etc " <tab> number1.xx<tab> number2.yy <tab> ... <CR/LF>
I have zero experience on SQL server or DTS, and very limited exposure to basic since 1985. Here's the transform script to date, it still chokes at the conversion point - CDbl produces a type-mismatch error. All I want to do is successfully insert whatever is found in column 3 as a decimal type into the 'units' table. If there's a better way, let's see it.
COL003 is selected as source, "units" is selected as destination for this transformation.
units: Type = Decimal, must not be null, size = 18, scale = 2
thisValue = (DTSSource("Col003")
IF IsNull( thisValue ) Then
thisValue = "00.00"
decimalValue = CDbl( thisValue )
DTSDestination("units") = decimalValue
Main = DTSTransformStat_OK
Platform: Windows 2000 Standard Server, M$SQL Server 2000 w/ Analysis Services.