I'm using VS2010 BIDS, importing from Excel 97-2003 .xls worksheet. I've got the following config:
Excel Source -> Conversion Split Transform -> OLE DB destination.
As long as I exclude the column in question the package runs and all columns import. However, if I enable the mapping for the column in question the excel source chokes on it.
The column in the source document contains letters, numbers, hash marks (#), and dollar signs($) etc. So, I have the destination column set to nvarchar(max) and have also tried nvarchar(255) up to nvarchar(800) but this column just won't import.
Within excel I have tried setting the column in question from General to Text format with no improvement. Because excel source chokes on this particular column I have found that adding a data conversion transform and doing a redirect on failure after the excel source transform to be useless.
Can you provide suggestions or explanation for what could be going on with this excel source column that makes ssis excel source transform choke on it???
Thanks in advance