SSIS Data Flow Conversion of Float to NVarchar importing null values

Question

I am trying to import an Excel 2007 spreadsheet into a staging table and I am encountering issues with some of the column types. Some of my columns have a combination of numeric and text values i.e. A column called Customer No can have numeric values such as 1234, 32432433, or text values i.e. A1000, ACC101TEXT. When I import my spreadsheet, the values with text in them are returning null whereas the numeric values are being imported properly. Moreover, the last row has a text description in this column that I need, however, after the import, this row value is null. Is there anything I can do to rectify the situation and import the spreadsheet as is? I've tried using data conversions from the data flow transformation, however, that is still not working. Please note, I cannot change or format the excel spreadsheet. Any help would be greatly appreciated.

Answer 1

You need to use the Advanced Editor to tell the excel source the column is a Unicode String, not a numeric. That is why alphanumeric fields are null.

1. Right-click on the Excel Source and choose advanced editor
2. Choose the fourth tab "Input and Output Properties"
3. Choose the column in question under the "Output Columns" and change the source to Unicode

That should fix your problem.