SSIS sets numeric columns as Text in Excel Destination

Question

I'm creating an SSIS package with an Excel destination. Initially it set external columns as DT_WSTR but I was able to change them to DT_R8. However, when running the extract to Excel, if the file is blank (only column headings), then it sets all columns as text. If there is data with numeric values in the first few rows, then the new added rows will have a numeric type, but if the destination file is blank then for some obscure reason they are set as text which affects applying formulas on the data.
Is there anyway to change the mode of the Excel Destination to "I've been doing this for many years so I know there should be a number here not text so please do not try to be too clever and change the data type". If at design time a column is set to DT_R8, why is the Excel Engine deciding for me that it should be formatted as text?

Answer 1

will have a numeric type

Excel don't have data type, a cell do have a value and a format information; with SSIS you pass only the value.

Answer 2

Hi @Gregorio Garcia ,

We can add IMEX=1 in the Excel Connection String.

Please refer to the following example and link:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Treating data as text using Microsoft.ACE.OLEDB.12.0

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I know this is an old thread now but in case it helps ... what we have done on occasion is to create two worksheets - one for dumping the data out to, and one to present the dumped data.
In the data-dump sheet, we insert a header row and a formatting row (dummy row, as referenced above), which contains a default value (e.g. 1234567890 for an integer, or 1,234,567.80 for decimal, or 01/01/1900 for date), and then we format the entire column as either a number (with no decimal places), as "accountancy" for monetary/decimal values, and as a "short date" for dates.
We then create a dynamic named range over the data dump using the OFFSET function.

In the presentation sheet, we reference the named range in a convenient cell like:
=IS(IFERROR(NamedRange),"No data", IF(ISBLANK(NamedRange),"",NamedRange))
Sometimes you might see 0's in blank cells, but you can either concatenate the NamedRange with an empty string (""&NamedRange), or you can set how you want empty cells displayed in the Excel Options > Advanced, and scroll down to "Display options for this worksheet" and uncheck "Show a zero in cells that have zero value".
Be aware that you can't have any editable cells "mid-range" otherwise you will receive a #SPILL error when Excel can't completely fill the desired range because it would otherwise "spill over" into ranges with existing values (even if you add those values after the named range). You can then add any other formatting you wish.

Not sure exactly how or why this works - suspect it's something similar to how SSIS determines data types when reading from a workbook range (it scans the first 8 rows, but this can be changed in the registry), so perhaps it scans the preceding row(s) where it intends to output data. But we've used this for the data types mentioned above and it works fine, and it means we don't have any need for manual intervention to correct these things, so we can fully automate the process.