Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBTYPE_STR to DBTYPE_NUMERIC Conversion Errors...Need Help! Expand / Collapse
Author
Message
Posted Friday, July 16, 2010 9:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 15, 2012 7:05 AM
Points: 44, Visits: 84
Hello Forum,
I have a file that I'm getting from some mainframe system that is a .csv format. I'm trying to import this file into my SQL Server data table and the values that are in this specific file is coming to me as a string. However the values are supposed to be Numeric (Double) to be exact.

Does any one know of a way to convert the data field in the DTS Transform Data Task for 2000 from a DBTYPE_String to a DBTYPE_Numeric?

I was trying to use a vbscripting task to do this, but I'm open to any suggestions at this point.

Thanks for any help,

Greg
Post #953955
Posted Friday, July 16, 2010 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 15, 2012 7:05 AM
Points: 44, Visits: 84
Ok, I figured out what was wrong with this for anyone readers that might be interested.

First of all, the data file that I received was from Excel and had the two offending columns formatted as currency. So apparently Excel saves these Currency columns with conditional formatting that SQL Server DTS does not like...ie: "$1,000.00" In this string field SQL Server was complaining about the $ and the , that was in that string. Also if there is a null value it puts "#Null" in the field. All of these things create problems I think for DTS to transform a string to a double data type.

I resolved by going to the source and explaining to them that they need to save their spreadsheet as a general format and not use any special formatting for display purposes before sending this file to me. I could have also opened the file myself and reformatted the spreadsheet as general before importing it into SQL 2000 using DTS.

I hope this my help another newby that experiences something similar trying to use a DataTransformation

Until next time...Greg
Post #954133
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse