enforcing data types in SSIS package

Question

I have a SSIS package that imports data from an excel file. One of the columns has mostly numbers (like 12345) but some also have letters (like 123A5)

When i configure the package i can't change the format and it's always recognized as double (probably it only looks at the first lines) .

How can i enforce my own data type , so that it reads the column as string ?

I've also tried to modify the package in visual studio and add the column in the data conversion step but it's still not working.

Answer 1

Yes, as you suspect the Excel driver is checking the first few rows to determine the data type. You can override the default of 8 rows using the MaxScanRows property:

http://support.microsoft.com/kb/236605

http://support.microsoft.com/kb/257819

Alternatively, save the Excel file as .csv first. It's much easier to specify data types for text files.