String Conversion in DTS using CDbl

Posted on 2006-05-17
Last Modified: 2008-01-09
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?
Question by:Reddgum
    LVL 9

    Accepted 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.
    LVL 3

    Author Comment

    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 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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now