XLS file with column Mixed INT and VARCHAR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

XLS file with column Mixed INT and VARCHAR

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-15 : 15:55:40

Hello
I have a bunch of XLS file there is a column in them called waferID
the first few columns have data like 10001099,100200199
then the data I need is below 95K2009N4033,95k2009N4032
when I do a preview of the data it shows all the 100%
but returns null for the 95k2009 stuff

any ideas

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-16 : 08:20:07

This may help you: http://developers.de/blogs/nadine_storandt/archive/2006/12/05/SQL-2005-SSIS-Excel-Source-Insert-NULL-Values-for-all-Records.aspx

"So the problem was SSIS Excel Source only look at the first 8 Rows if they contain the same value SSIS decide to use the same data type for all entries.

To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:

Excel 8.0;HDR=YES;IMEX=1

The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources