excel datasource returning nulls with sql command

Question

I'm reading excel sheet that has a column formated as number but every now and then contains letters. These are returned as NULLS. I need the values. I added IMEX=1 and that did not help. Changing Values in the registry is not an option. the columns are fixed so is there a way to manually set them to string?

I also tried changing it from advance editor and they go back to the default that is double float.

I am getting the data with an SQL command. Is there a way to force the column to be a type with cast or convert from here? I tried with cast and I keep getting an error.

Answer 1

I have found out that cast and convert does not work in this context. I used CSTR(). I was getting error displaying this cell in the preview but the end product was good.

Answer 2

Try reading in the columns as varchar, then either use a data conversion or derived column transformation within the SSIS package, or update the data once read into the db using cast or convert.