I am setting up a simple data flow task to pull in multiple CSVs to a SQL Server DB table. A number of columns in the CSV are float but occasionally will say 'N/A*'. I want to keep these columns as floats and make any string in them convert to NULL.
By just ignoring the conversion failure in the flat file source settings, I can achieve this, but I don't think it's best practice to ignore failures. Is the solution just to do a Conditional Split on all these columns to say when 'N/A*' then NULL, else float?
You could use a Derived Column to replace the column with a Float column that replaces "N/A" with NULL.