Solved

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

Posted on 2004-08-25
10
324 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 Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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 125 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 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