x

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?

more ▼

asked Nov 13, 2009 at 07:09 PM in Default

Bret gravatar image

Bret
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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

more ▼

answered Nov 18, 2009 at 12:00 AM

Elliott W gravatar image

Elliott W
26

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 14, 2009 at 11:22 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

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
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 17, 2009 at 01:38 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 31, 2009 at 09:01 PM

WayneS gravatar image

WayneS
31

(comments are locked)
10|1200 characters needed characters left

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

http://support.microsoft.com/kb/257819

more ▼

answered Jan 11, 2010 at 01:44 PM

Russ Bell gravatar image

Russ Bell
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x943
x117

asked: Nov 13, 2009 at 07:09 PM

Seen: 20358 times

Last Updated: Feb 22, 2013 at 06:35 AM