Unanswered: importing text files and converting datatypes
Hi ya... About to tear my hair out.
I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.
I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.
Yup. This particular DTS package works with most of my text files, and it used to work with this particular text file, but now all of a sudden it won't convert imported varchars (or chars, or nchars, or nvarchars) to floats, or monies. And to be clear, I tried importing the file as all varchars, and then in a later step in my dts packaged, tried to cast those particular fields as floats. And that's when I get that error.
I think the varchar column may have values that sql cannot convert to money/float. You may have to check the varchar column prior conversion with functions as ISNUMERIC or a where statement that checks (not like '%[^0-9]%'). You may need to consider this in your package, possibly have it send a notification. The rows that do not fit, what are the options?