?
Solved

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

Posted on 2007-10-11
8
Medium Priority
?
664 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:pwanveer
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20061823
The usual approach is:

If IsNumeric(DTSSource("Col021")) Then
   DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
Else
   DTSDestination("POST_BAM") = 0
End If
0
 

Author Comment

by:pwanveer
ID: 20061860
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 252 total points
ID: 20061987
I usually import the file into a staging table which is all VARCHAR and then do a data conversion there.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20062005
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 248 total points
ID: 20062081
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
 

Author Comment

by:pwanveer
ID: 20062097
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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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