I have a package in SSIS 2008 with two tasks:
Task 1 is a CREATE TABLE statement executed in an Excel 2007 spreadsheet. It creates several columns of type Currency. (this task was generated by the Import and Export Wizad).
Task 2 is a Data Flow that extracts data from a view and sends it to the table just created.
If I then open the spreadsheet and view the data (or if I try to them IMPORT the data using SSIS) I see that those columns that were defined as Type "Currency" in the CREATE TABLE statement are in fact WSTR (nvarchar).
In Excel, it shows the little exclamation icon next to the cell with the message: "The number in this cell is formatted as text or preceded by an apostrophe."
This happens if using either an Excel 2007 Connection Manager or an OLE DB Connection Manager with "MS Office 12.0 Access Db ..." Provider.