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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy