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.

• http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx

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="...";

Well IMEX=1 did not work for me. Neither did Reynier Booysen's suggestion. (I don't know if it makes a difference but I'm using SQL Server 2008r2). A good explanation of some workarounds and also some explanations of why IMEX=1 is limited to the first eight rows of each spreadsheet can be found at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/78b87712-8ffe-4c72-914b-f1c031ba6c75

Hope this helps

I saw your question today, I was having the same problem and I found the EASIET way is to save the excel sheet as 97-2003 format and the import will keep the columns with the same data type you specified. Hope this helps!

Option 1. Use Visual Basic to iterate through each column and format each column as Text.

Use the Text-to-Columns menu, don't change the delimination, and change "General" to "Text"

I had the same problem. The problem sit in the Excel Source task. When you setup this task the first time, the task will connect to the specified Excel file (via the Excel connection) and decide what type each column is based on the current spreadsheet.

Thus, if you set up the Excel Source task, just make sure that the columns that should be text only has text in the column. This means that the Excel Source task will always assume that any subsequent spreadsheets will have the same format and will read 12345 as text because the column was text when the task was set up.

Hope it makes sense!

It took me a bit to realize the source of the error in my package. Ultimately I found that data was converted to null (Example: from "06" to "NULL"), and I found this via Preview in the source file connection (Excel Source> Edit> Connection Manager> Sheet='MySheet'> Preview...). I got excited when I read the post by James to edit the connection string to have extended properties: ;Extended Properties="IMEX=1". But that did not work for me.

I was able to resolve the error by changing the Cell Format in Excel worksheet from “Number” to “Text”. After changing the format, the upload process ran successfully! My connection string looks like: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\myServer\d$\Folder1\Folder2\myFile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=NO";

Here is are some screenshots that resolved my error message.

Error: Metadata of Excel file connection

Source of error: “General” format

Source of error changed: “Text” format

Error fixed: Metadata of Excel file connection