July 16, 2010 at 9:19 am
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
July 16, 2010 at 1:18 pm
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:cool:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply