SQL Server Home
Excel Data Types from SSIS
as Won't Fix
7/28/2009 12:20:27 PM
User(s) can reproduce this bug
I have a package in SSIS 2008 with two tasks:
Task 1 is a CREATE TABLE statement executed in an Excel 2007 spreadsheet. It creates several columns of type Currency. (this task was generated by the Import and Export Wizad).
Task 2 is a Data Flow that extracts data from a view and sends it to the table just created.
If I then open the spreadsheet and view the data (or if I try to them IMPORT the data using SSIS) I see that those columns that were defined as Type "Currency" in the CREATE TABLE statement are in fact WSTR (nvarchar).
In Excel, it shows the little exclamation icon next to the cell with the message: "The number in this cell is formatted as text or preceded by an apostrophe."
This happens if using either an Excel 2007 Connection Manager or an OLE DB Connection Manager with "MS Office 12.0 Access Db ..." Provider.
SQL Server 2008 - Standard Edition
Integration Services (DTS)
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
Use the Export Wizard to extract data with currency data types and send it to an Excel table. Make sure the wizard has a CREATE TABLE statement executed prior to the data flow.
The Excel table has all its data in WSTR data types.
I expect that when I issue a CREATE TABLE statement and SPECIFY DATA TYPES, that Excel would honor those datatypes in the table.
I CERTAINLY don't expect Excel to format all those numbers as text!
to post a comment.
Please enter a comment.
on 12/11/2013 at 3:41 PM
Right now, I can't create a good Excel file from SSIS any more. So disappointed. I don't know should I laugh or cry at the status: Status: Closed as Won't Fix!
on 7/21/2013 at 3:58 AM
Sorry, but I can't see any "changes being made" in SQL Server 2012... The way SSIS works with ACE provider is really a mess!! Please, could you reopen this bug and try to solve it??
on 7/23/2010 at 5:04 PM
Thank you for your feedback. We believe that changes being made in an upcoming release will alleviate the concern you expressed; and therefore, we are closing this issue. As always, you may request that this issue be reopened or that new issues be filed at any time. Please continue to provide us with feedback. Your support is essential to our success.
-- The SSIS Team
Jason V Campbell
on 5/24/2010 at 6:38 AM
It's been almost a year since this issue was raised. Can Microsoft give feedback to the issue. I'm having a similar problem.
on 8/10/2009 at 1:19 PM
Thank you for your submission. We will be reviewing your information and providing you feedback on our findings and progress. Thanks for your interest and support of SSIS.
on 7/31/2009 at 8:34 AM
In the 24 hours since this post, I have some additional information:
If you use BIDS 2005, and an Excel 2003 Connection Manager, then you can click the New button in the Excel Destination on the data flow and create the table that way. This works. But if you use an OLE DB Connection Manager, with the ACE provider and specify "Excel 12.0" in Extended properties (standard connection when working with Excel 2007 files) and follow the same steps: Click New in the Destinatin Connection Manager then click OK to run the CREATE TABLE statement, the resulting data will all come in as text.
If you use BIDS 2008 and Excel 2007, when you click the New under the Destination connection, you get the message: "There is no sufficient information about mapping SSIS types of data types of the selected .NET data provider. As a result, you may need to modify the default column types of the SQL statement on the next screen." It does eventually create the table, but if you check the data types on the mapping page, you will see that they are all DT_WSTR.
So it appears that Excel 2003 works, but any variation of Excel 2007 (native Excel Connection or OLE DB connection) does NOT.
on 7/28/2009 at 12:35 PM
As the author of this post, I am adding additional comments immediately after submission. I am trying to separate the facts of the issue, as outlined in the Description section, from my opinions. What you are about to read is rather harsh:
It appears to me that Integration Services has been taking steps BACKWARD in regard to dealing with Excel. DTS was REALLY easy to import and export to and from Excel. When SSIS came along, everything changed, and NOT for the better.
How can Microsoft claim it has an 'Enterprise class' tool in Integration Services when the tool seem to NOT honor user specified data types in cases such as this. I have submitted other bugs regarding SSIS and most of them are in reference to either Excel, or Data Types (SSIS 2008 and Derived Column Transform).
If you are reading this, ask yourself this: If you ordered a mushroom pizza at the pizza parlor, would you be satisfied if they served you sausage instead, with no notice? Then why should I as a user of SSIS be forced to accept W_STR data types (which cannot be aggregated or pivoted, etc) when I ordered (CREATE TABLE ...) Currency data types?
Microsoft need to fix this and fix it right, or don't call your product 'Enterprise class'.
I appologize for the rant. But seriously, it's real frustrating to NOT have SSIS do what I tell it, and even what the Export Wizard does!
to post a workaround.
Please enter a workaround.
on 7/15/2011 at 7:11 AM
Look at the metadata SSIS determines for your excel destination. It does not matter which cell format you define in excel. SSIS scans the data in the sheet. I'm not sure but I think that it looks at the last row in the sheet that does contain anything (data and formatting) and decides then whether to adress it as string, number or whatever.
So take care to leave a row containing numeric data (it may even be hidden from view) and SSIS should be able to determine correct data types
© 2014 Microsoft