Solved

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

Posted on 2004-08-25
10
294 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now