Why does SSIS Data Flow to Excel think my Number is Text?
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?
Best Answer
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
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.
http://sqltechi.blogspot.in/2012/12/ssis-basics-setting-up-your-initial.html hope it might help you
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.
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.
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
This worked for me.
From MSDN: " The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. " http://msdn.microsoft.com/en-us/library/ms141683.aspx http://support.microsoft.com/?scid=kb;en-us;194124&x=9&y=12
Andrea
Hi... i had the same problem... In my ETL my source is an Excel and has a column that first show a numbers but in last cells have a varchar data type..so when i import this infomration in a table the varchar type appear like a NULL i put de Imex = 1 but in my case this solucion is not appropiated for me.. do you known a different fort for de varchar doesn't appear like NULL
