SSIS Excel Import Forcing Incorrect Column Type

Question

I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.

Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly?

EDIT: I found this: http://support.microsoft.com/kb/236605

I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. Instead of not importing character data, it stopped importing purely numeric data. Apparently someone doesn't think 12345 can be represented as a string...

Answer 1

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

Answer 2

1. Click File on the ribbon menu, and then click on Options.

2. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.

3. Click OK.

4. In the worksheet, select the cells that you want to format.

5. On the Home tab, click the Dialog Box Launcher Button image next to Number.

6. In the Category box, click Number.

7. In the Decimal places box, enter the number of decimal places that you want to display.

Answer 3

This worked for me. Select the problematic column in Excel - highlight the whole column. Change the format to "Text". Save the Excel file.

In your SSIS package, go to the Data Flow pane for your import. Double click the Excel Source node. It should warn you that the types have changed and ask you if you want to remap them. Click Yes. Executing should now work and bring in all values.

Note: I'm using Excel 2013 and Visual Studio 2015, but I assume these instructions would work for earlier versions too.

Answer 4

Another workaround is to sort the spreadsheet with the character data at the top, thereby causing Excel to see the column as string, and importing everything as such.

Answer 5

If multiple columns in the excel spreadsheet present with the same name, this kind of error occurs. The package will work after making the column name's distinct. Sometime the hidden columns are being ignored while checking the columnn names.

Answer 6

I had the same issue, multiple data type values in single column, package load only numeric values. Remains all it updated as null.

Solution

To fix this changing the excel data type is one of the solution. In Excel Copy the column data and paste in different file. Delete that column and insert new column as Text datatype and paste that copied data in new column.

Now in ssis package delete and recreate the Excel source and destination table change the column data type as varchar.

This will work.