Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Importing null string field into decimal field-Text file to MS SQL Server 2000

Posted on 2004-08-25
10
Medium Priority
?
340 Views
Last Modified: 2012-05-05
I have a text file which has records with blanks in a particular column.  When I try to import these records into a decimal field I get an error. I would like to replace these blank fields with a 0 when they are imported.   Here is the ActiveX script I'm currently using. The error I get when the import reaches such a record is "Transformation encountered an invalid data value for destination column."

Function Main()
      DTSDestination("FrBCPM") = SourceOr0("Col098")
      Main = DTSTransformStat_OK
End Function


Function SourceOr0(Col098)
     if IsNull(DTSSource(Col098)) then
        SourceOr0 = 0
     else
        SourceOr0 = DTSSource(Col098)
     end if
     
end Function

What is wrong with my script?
0
Comment
Question by:CTerreri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11898133
Try:

Function Main()
     DTSDestination("FrBCPM") = SourceOr0(DTSSource(Col098))
     Main = DTSTransformStat_OK
End Function

Function SourceOr0(Col098)
     If IsNull(Col098) Or Trim(Col098) = "" Then
        SourceOr0 = 0
     Else
        SourceOr0 = Col098
     End If
End Function
0
 

Author Comment

by:CTerreri
ID: 11898182
This converts all of my fields to 0???
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11898455
Sorry, I left out the quote marks on the first column reference. This corrects that and specifies the datatype on the variable coming into the function, the function, and changes the output to a decimal:

Function Main()
     DTSDestination("FrBCPM") = SourceOr0(DTSSource("Col098"))
     Main = DTSTransformStat_OK
End Function

Function SourceOr0(Col098 as String) as Decimal
     If IsNull(Col098) Or Trim(Col098) = "" Then
        SourceOr0 = 0
     Else
        SourceOr0 = CDec(Col098)
     End If
End Function
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:CTerreri
ID: 11908093
This code won't compile. Says I need an extra ')'.  Still working on.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11908314
I don't see a problem with parentheses, but I had the If wrong, which might have left to that misleading error message. Try this:

Function Main()
     DTSDestination("FrBCPM") = SourceOr0(DTSSource("Col098"))
     Main = DTSTransformStat_OK
End Function

Function SourceOr0(Col098 as String) as Decimal
     If IsNull(Col098) = True Or Trim(Col098) = "" Then
        SourceOr0 = 0
     Else
        SourceOr0 = CDec(Col098)
     End If
End Function
0
 

Author Comment

by:CTerreri
ID: 11908346
It does not like this line:

Function SourceOr0(Col098 as String) as Decimal

Says it's expecting ')'  Is this code written incorrectly?
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 11908505
Sorry for the previous unworkable code. The following works, if you set the destination data type to decimal and all the values in the source table can be implicitly converted to decimal values by SQL Server.

Function Main()
     If IsNull(DTSSource("Col098")) = True Or Trim(DTSSource("Col098")) = "" Then
        DTSDestination("FrBCPM") = 0
     Else
        DTSDestination("FrBCPM") =  DTSSource("Col098")
     End If
     Main = DTSTransformStat_OK
End Function
0
 

Author Comment

by:CTerreri
ID: 11908598
Yes this works!!!  But I have a final question.  I have approximately 20 source columns that need this if statement.  Is there a way to create 1 function for all of them?

Thanks
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11908663
I haven't been able to find a way to get a second function to work via the DTS wizard. I suspect you could do it if you coded the whole DTS project in Visual Basic, but otherwise I think you'll have to repeat the If for each column.
0
 

Author Comment

by:CTerreri
ID: 11908719
OK.  Thanks for considering.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

597 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