I have an excel spreadsheet with a column "Premium", number format, 2 decimal places.
I have a table with "Premium" field, numeric(10,2).
I created a SSIS data flow from the table (OLE Source) to the spreadsheet (Excel Destination).
the task runs fine, but when I open the spreadsheet, the "Premium" column has an error note stating that I have a text value in a column formatted for number. the exported numbers look good (100.01), but I cannot sum them or get rid of the error without manual intervention.
What can I do to get Excel to recognize the database table number as numeric?
asked Nov 13 '09 at 07:09 PM in Default
This actually looks like a defect with the Excel output. I have forced the types to be decimal(18,2) in the source and the destination. It says that everywhere but then when it is written to the file it prepends a "'" to the front of each row. It just doesn't want to work. I do think that this MS connect item is related to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=477970
answered Nov 18 '09 at 12:00 AM
What decimal separator is set in "Regional and language options" for your windows account? Does it differ between your "system account" (or your service account for SQL Server) and your windows account?
In Sweden we use , and not . as decimal separator but most SQL servers are insalled under an account with . as the decimal separator and that causes problems sometimes, like the one you have in Excel.
answered Nov 14 '09 at 11:22 AM
What does SSIS indicate for the data types for the source and destination columns? If the source data type is wrong, try using a Data Conversion task. If the destination data type is wrong, try formatting the column in the spreadsheet appropriately.
answered Nov 17 '09 at 01:38 AM
Right-click on the Excel Destination, and select "Show Advanced Editor". Change to the "Input and Output Properties" tab. Expand the "Excel Destination Input" and "External Columns". Click on the "Premium" column. The Data Type is probably "Unicode string [DT_WSTR]". Change it to an appropriate numeric type.
answered Dec 31 '09 at 09:01 PM