|
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?
(comments are locked)
|
|
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 CEWII
(comments are locked)
|
|
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. I am running everything from my laptop, and I am having problems with both numbers with decimal places and those without. When I cut and pastefrom SQL query analyzer, everything is fine, but populating the spreadsheet via SSIS causes the issue. thanks for your help.
Nov 16, 2009 at 06:50 PM
Bret
http://sqltechi.blogspot.in/2012/12/ssis-basics-setting-up-your-initial.html hope it might help you
Feb 22, 2013 at 06:35 AM
askmlx121
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
I have not tried to write data to Excel using SSIS, but I had a similar problem using MS Access and VBA. The problem turned out to be the way Excel itself handles data types. It has some built in logic for "guessing" the datatype based on a sample size. If you have a lot of null values early in the output set it may interpret it as character data. There is a registry parameter named MAXSCANROWS that controls this behavior. There is also an import mode on many connection strings called IMEX that is related to data typing. This can be a frustrating thing to deal with because it depends on your actual data and the order that it is received. Setting MAXSCANSROW=0 can force it to evaluate all rows, but it may have performance considerations. I suggest that you look at this link to see if it fits your situation. If it does not fit exactly, it may get you on the right track. How To Use ADO with Excel Data from Visual Basic or VBA
(comments are locked)
|
1 2 next page »
