importing null value into "real" datatype column via dts -- MS Sql Server 2000

Posted on 2007-10-16
Last Modified: 2008-01-09
MS SQL Server 2000

I have a tab-delimited text file with a header row, the last 3 columns of which are of the data type "real".  The last column needs to be imported as null.  I am trying to import this file through DTS.

A row in the tab-delimited text file looks like this:

info <tab> info <tab> 0 <tab> 0 <tab> null

I am getting an error during the DTS process:

"Conversion invalid for datatypes on column pair 5 (source column 'nDistance' (DBTYPE_STR),destination column 'nDistance' (DBTYPE_R4)).

I don't understand why it's seeing the last column as a string.

How can I solve this?
Question by:g_johnson
    LVL 15

    Accepted Solution

    Is the actual value 'null'. If so, DTS will see it as a string. You will need to use an activex script to do the transformation and use something like:

    If DTSSource("col005") = "null" Then
        DTSDestination("myCol5") = Null
        DTSDestination("myCol5") = DTSSource("col005")
    End If
    LVL 17

    Expert Comment

    Since it is a text file DTS / SSIS will interpret NULL as a literal string.  You can use the conversion scheme that dbbishop talked about or you could modify the text file to replace NULL with a zero, if that is appropriate.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    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.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now