• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

String Conversion in DTS using CDbl

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

Data File:
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

Function Main()
      thisValue = (DTSSource("Col003")
      IF IsNull( thisValue ) Then
            thisValue = "00.00"
      END IF
      decimalValue = CDbl( thisValue )
      DTSDestination("units") = decimalValue
      Main = DTSTransformStat_OK
End Function

Platform: Windows 2000 Standard Server, M$SQL Server 2000 w/ Analysis Services.

Any ideas?
1 Solution
looking at your code, here's a revision:

Function Main()
dim decimalValue
dim thisValue

     thisValue = DTSSource("Col003")
     If IsNull( thisValue ) Then
          thisValue ="00.00"
          DTSDestination("units") = CDbl( thisValue )
     decimalValue = CDbl( thisValue )
     DTSDestination("units") = decimalValue
     End If
     Main = DTSTransformStat_OK
End Function

Hope this helps.
ReddgumAuthor Commented:
Well, serves me right for assuming the input data was correct -- the actual problem was that every 200 or so records, there was a text field entered instead of numeric data...so naturally, the script chokes.

I'll give you the points anyway - all I needed to do was add a type check and it disregarded the invalid records.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now