11

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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 9:44

3 Answers 3

3

Step 1: Add IMEX=1; MAXROWSTOSCAN=0 in the connections string (e.g. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Source\filename.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1; MAXROWSTOSCAN=0").

Step 2: Close the solution. Open the SSIS package in text editor, find the columns that kept reversing back to the pre defined data type. Update it manually in the XML file to the desired format.

Step 3: Open up the package in SQL Server Data Tool and execute, now you should be able to load the data as it is from the file.

3
  • Sorry, but the source data type is not in the package. Only the destination column types. This defeats the purpose again, as conversion still happens in side the Microsoft.Ace driver (or whichever driver is chosen), and data loss has already happened by the time it get into SSIS and the destination table. Dec 7, 2016 at 14:55
  • this solved my issue
    – Baldie47
    May 19, 2021 at 12:27
  • thank you! you saved my day!
    – Andrey
    Jan 13, 2022 at 14:52
2

Is it possible to get the file as a .csv file which can be imported like a text file and will not have this problem? I generally kick back all Excel files and ask for another format because SSIS and Excel do not play well together on many levels.

1
  • 1
    I'm hopping the change to .csv or txt file is in the near future. will make things much easier.
    – Juan Velez
    Apr 11, 2013 at 14:02
2

It appears there is no way to fix this issue. Excel looks at the first few rows of data in the column and will always FORCE the datatype to FLOAT no matter what. I was given a solution of creating a template with a few varchar characters in the first few rows (8+) so EXCEL will pick that up as the data type and then import my data. On thing that helped is since I am using Source Safe I am able to "Check In" a file so that it makes my package file "Read Only" so that Visual Studio can not change my pre defined data types.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.