There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].
I can change it manually in the Output branch's External and Output columns, but the Error Output simply refuses to let me change the respective column.
Error at Extract Stations [Excel Source [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Group No" (29)".
Error at Extract Stations [Excel Source [1]]: Failed to set property "DataType" on "output column "Group No" (29)".
I have tried modifying the package xml. I tried the IMEX=1
and typeguessrow=0
but none of that has fixed my problem. Does this have any fix at all? There are also articles suggesting you modify your registry so that excel reads more then the default 8 rows before guessing the data type. I do not want to go this route because even if it were to work, then I would have to modify the registry on the machine I plan to run the package on.
The excel field to be imported into the SQL nvarchar field reads for example
295.3
296.33
but they are being written to the SQL table as
295.30000000000001
296.32999999999998
I put in dataviewers and the fields show
295.3
296.33
all the way thru execution which is correct but I guess when it hits the OLE DB Destination source it somehow converts it to the
295.30000000000001
296.32999999999998
which is wrong.
I also have values such as V321.1 and V213.34 for example which are definitively not integers and need to be stored as varchar.