Excel Data Types from SSIS - by Todd C

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 477970 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 7/28/2009 12:20:27 PM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Hieu Thai 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!
Posted by Luca Zavarella 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??
Thank you.
Posted by Microsoft 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
Posted by 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.
Posted by Microsoft 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.
Posted by Todd C 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.
Posted by Todd C 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!