Text file value to SQL table via DTS (VBScript transformation) / mismatch: '[string: " "]'

This must be very easy.  I can't believe I can't figure it out.
I have a fixed length text file I am importing into a SQL table via DTS. This file contains both header and footer records. DTSSource("Col021") = DTSDestination("POST_BAM"); the post_bam field is data type float. I am getting a Data Type mismatch error in translating the footer record. I have attempted a number of things to modify the value being delivered to the destination to strip out the bad values. (In this case I believe they are spaces.)
I've tried the following:

If IsNull(DTSSource("Col021")) Then DTSDestination("POST_BAM") = 0
   IsEmpty(DTSSource("Col021"))
   IsNumeric(DTSSource("Col021")) =FALSE
   DTSSource("Col021") = (" ")
   Instr(DTSSource("Col021"),Chr(32)) > 0

All of the scripts above return the same error:
Error Discription: Type mismatch: '[string: "                "]'
Can anyone point me in the right direction? Thank you.
pwanveerAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
I usually import the file into a staging table which is all VARCHAR and then do a data conversion there.
0
 
Anthony PerkinsCommented:
The usual approach is:

If IsNumeric(DTSSource("Col021")) Then
   DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
Else
   DTSDestination("POST_BAM") = 0
End If
0
 
pwanveerAuthor Commented:
Sorry, I paired down the IF THEN for simplicities sake. This is the actual Function. It already takes into account the CDbl converstion.


Function Main()

    If (IsNull (DTSSource ("Col020")) Or IsEmpty (DTSSource("Col020")) Or DTSSource("Col020") = (" "))  Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))

   ElseIf IsNull(DTSSource("Col021")) Then
        DTSDestination("POST_BAM") = 0

   ElseIf Trim(DTSSource ("Col020")) = "-" AND CDbl(DTSSource("Col021"))>0 Then  
        DTSDestination("POST_BAM") = Cdbl(DTSSource("Col021")) * (-1)

    End If

    Main = DTSTransformStat_OK

End Function
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
I would suggest you take a second look at this:
    If (IsNull (DTSSource ("Col020")) Or IsEmpty (DTSSource("Col020")) Or DTSSource("Col020") = (" "))  Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
0
 
Anthony PerkinsConnect With a Mentor Commented:
This is how I would write it (not knowing all the details):

Function Main()

If IsNull(DTSSource("Col020")) Or IsEmpty(DTSSource("Col020")) Or DTSSource("Col020") = " "  Then
      If IsNumeric(DTSSource("Col021")) Then
            DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
      End if
ElseIf IsNull(DTSSource("Col021")) Then
      DTSDestination("POST_BAM") = 0
ElseIf Trim(DTSSource ("Col020")) = "-" Then
      If IsNumeric(DTSSource("Col021")) Then      
            If CDbl(DTSSource("Col021")) > 0 Then  
                  DTSDestination("POST_BAM") = -Cdbl(DTSSource("Col021"))
            End If
      End If
End If

Main = DTSTransformStat_OK

End Function
0
 
pwanveerAuthor Commented:
nmcdermaid: I considered that. I just thought this would be a simple transformation so I could get rid of a step or two.

acperkins: Actually, when you get rid of the footer record that step works fine. (Even though it's pretty ugly.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.