Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Let me make sure I'm clear you are pulling from the Excel document and importing into SQL server correct? If so what is the destination column's data type? –  Zane Apr 4 '13 at 15:57
    
@zane Yes that is right. –  JuanVelez Apr 4 '13 at 15:58
1  
What's the data type for the column destination. –  Zane Apr 4 '13 at 16:03
2  
You may not be looking to do mathematical operations on these however they are still numeric values and if the column was stored as such you may not be having this conversion problem. –  Zane Apr 4 '13 at 18:24
1  
Have to tried changing the excel column type to be string and not a number in excel? –  Zane Apr 4 '13 at 20:46

2 Answers 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.

share|improve this answer
1  
I'm hopping the change to .csv or txt file is in the near future. will make things much easier. –  JuanVelez Apr 11 '13 at 14:02

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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