P Jones (1/2/2013)
Try bringing in the decimal field as varchar(50) and then using a derived column transformation to convert it rather than doing everything in the flat file connection manager. You should then be able to catch the error.
Or bring it to a staging table that is unconverted (all fields varchar for example) then convert it as a second stage which is what I do for a big daily import, combining unformated data from several flat files into each staging table.
If the conversion is then part of an input select statement eg select convert(decimal(9,9), myvalue) etc
then use a case statement to validate the number
e.g. select case when isnumeric(myvalue) then convert(decimal(9,9), myvalue) else null end....
and so on.
I found this is also useful with dates (using isdate())
Getting slightly off-topic, but IsNumeric is likely to give you some false positives. Try these, for example:
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.