SSIS Excel Import Forcing Incorrect Column Type
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.
You can convert (ie. force) the column data to text...Try this (Note: These instructions are based on Excel 2007)...
The following steps should force Excel to treat the column as text:
Open your spreadsheet with Excel.
Select the whole column that contains your "mostly numeric data" by clicking on the column header.
Click on the Data tab on the ribbon menu.
Select Text to Columns. This will bring up the Convert Text to Columns Wizard.
-On Step 1: Click Next
-On Step 2: Click Next
-On Step 3: Select Text and click Finish
Save your Excel sheet.
Retry the import using the SQL Server 2005 Import Data Wizard.
Also, here's a link to another question which has additional responses:
Import Data Wizard Does Not Like Data Type I Choose For A Column
One thing that isn't mentioned in the accepted answer is that the "IMEX=1" parameter has to go inside the quoted portion of:
...;Extended Properties="...";