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.

Please help! :mad:

ThanksI'm not totaly clear on this: so the data is inside the table 'Pol_Dump073104' and a convert(money, benefit) fails with "Error converting datatype char to money"?|||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.

It's frustrating!!!

Am I making any sense?|||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?|||Thanks so much kaiowas. I found the bad rows. Now, how can I tell SQL to send me an exception report with the missing rows?|||Kaiowas, your help saved me. I figured everything out, and it's working beautifully... exception report and all. Just needed to be pointed towards the right track. Many thanks.sql