Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-08-25
10
Medium Priority
?
341 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
  • 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
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!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

569 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