I have an Excel spreadsheet whose first column is mostly numbers or a combination of letters and numbers. These are codes relevant to my business area:
28 38 48 L2 A7 BC etc.
The column is formatted as text in Excel. I want to use this as a data source in SSIS for SQL Server, but when I add the object to a Data Flow designer, it forces a cast of the column to a float. Always.
Using the advanced properties on the Excel source, I've tried to set the input and output columns as unicode strings for the data type, but this doesn't work. I've also tried other string/text formats. No luck.
Next, I have tried a SQL select statement, using the CStr() function to attempt to force a conversion to a string of the first column. The Excel data source can't cope.
No matter what, the provider used by the Excel data source is going to force the column to a number, which means I end up a lot of NULL values for those cases like A7, B6, etc. because they don't make the cast.
Is there any way to control this? I'm probably better off saving my Excel file as a CSV, but it seems like the OLEDB Jet provider shouldn't choke on something so simple and having to fall back on flat files is lame.
Many thanks in advance for your help.