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