DTS, Excel and mixed format columns

I recently had a problem DTS importing an Excel spreadsheet which contained columns with mixed text and data values. After a bit of hunting around, I found an extended property IMEX=1. The IMEX=1 property forces everything to text when reading from a source.

Easy to change in your connection string, but how do you edit it in DTS? Answer: Dynamic Properties.


  • Add a Dynamic Properties step (before the copy data step)
  • Add a new "change" for "Your Connection > OLE DB Properties > Extended Properties"
  • Give it a constant value of Excel 8.0;HDR=YES;IMEX=1
Author Paul Hayman

Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.

Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.


Anonymous said:

I've been trying to fix this for 2 hours now, reading all these potential solutions on the web.

But all I needed was your short article.

Thx so much!

18/Aug/2006 19:43 PM

Paul Hayman said:

Glad to help

18/Aug/2006 20:19 PM

Mastermnd said:

Thank you! Best (simplest) explanation I've read on the web about this so far.

28/Feb/2007 09:04 AM

dabuskol said:

It does work for some fields but my site no which have data such as '177-11' is missing from the field even trying to put IMEX=1.

Need your help..

17/Jun/2007 09:26 AM

dabuskol said:

not working for me even added IMEX=1 such as '177-22' is missing.

17/Jun/2007 09:28 AM

Sudesh said:

Gr8!, this works fine in my case

13/Jul/2007 11:47 AM


Thanks this worked great!

13/Oct/2009 20:08 PM

Add Comment


Your comment has been received and will be shown once it passes moderation.