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

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

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?
1 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
Chris MangusDatabase AdministratorCommented:
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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