I’ve seen this issue before, it’s Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it’s not.
;Extended Properties="IMEX=1"
It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.
- http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx