Solved

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

Posted on 2004-08-25
10
319 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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