I am using SSIS to import a monthly Excel file of product sales data.  One of the columns in Excel is a numeric string containing product number.  Sometimes this number begins with a zero, so I need it to be interpreted as text.  When I receive the file, a few of the rows have an empty product number so I need to type it in.  I notice that when I import this thru SSIS these values come over as NULL.  I've tried a few things on the Excel file side before importing, to no avail, such as changing the Format Cells in Excel for the column to Text (as opposed to General).  I also tried creating a new column, formatting it as Text, then copying the original column of product number values into that new column and using that for the import.  On the SSIS side, I have used an Image View to see at what point the values are getting put to NULL, and it is in the very first step, that being the Data Flow Component extracting from the Excel Source.  In the Advanced Editor of that step, the column is set to type DT_WSTR (255) for both External Columns and Output Columns.  It won't seem to allow anything other than that.  I'm thinking the solution has to be in how I am preparing the data on the Excel side, but not sure what to try.  It is only the rows that I manually add in a product number that are causing me an issue.

I've attached a copy of the Excel file, and highlighted those I've typed in manually and which are coming over as NULL.  I've also attached a screen print of the Excel step in SSIS in case that helps.

By the way, I have a very similar issue with another import when I'm bringing over zip code data.
ProductSales.xls
SSIS.jpg