SSIS Excel Destination results in error opening excel file

Question

question

paws27284 asked May 19, '14

SSIS Excel Destination results in error opening excel file

I am using SSIS data flow task to create an Excel file. The source is a sql 2008 r2 table with transformations for those darn unicode variables. The Destinion is the excel flow item (as .xls). This works great. However the problem appears when I open the excel file in Office 2010. I get the pop up box with "File Error: Data may have been lost". This is causing an issue because the next step to read the file cannot be automated if a dialog box pops up. I have perused the site looking for insights, but have not seen this exact issue. I have found a reported problem between SSRS and Excel 2010 that does result in the same error I am getting. It has to do with Excel writing -0 during the export. There is a fix for SQL 2008 r2, but I am not sure it would fix my problem becasue it's in SSIS not SSRS. Thanks for any support! Paws27284

ssis excel

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

paws27284 commented · May 21, 2014 at 06:54 PM

I tried the same Export with the Import/Export wizard within SSMS and the result was the same. When I opened the excel file I received the dialog box "File Error: Data may have been lost". Paws27284

0 ·

Answer 1 · 2014-05-22T12:18:35Z

nopol answered May 22, '14

If I understand your question well, the error is due to the fact that your produce an xls file (97-2003) and open it with excel 2010. In your ssis package I would try to use an ole db destination instead of an excel destination, choosing the Microsoft office 12 Access database engine provider to produce an xlsx file. An article outlining how to proceed can be found [here][1] [1]: http://If+I+understand+your+question+well,+the+error+is+due+to+the+fact+that+your+produce+an+xls+file+(97-2003)+and+open+it+with+excel+2010.+In+your+ssis+package+I+would+try+to+use+and+ole+db+destination,++choosing+the+Microsoft+office+12+Access+database+engine+provider+to+produce+an+xlsx+file.+An+article+outlining+how+to+proceed+can+be+found+here

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

paws27284 commented · May 22, 2014 at 01:13 PM

Thanks, I'll give it a try. But the link in the answer takes me to an error. Paws27284

0 ·

nopol commented · May 23, 2014 at 04:16 PM

http://dataintegrity.wordpress.com/2009/10/16/xlsx/#more-180

0 ·

paws27284 commented · May 28, 2014 at 02:19 PM

I tried this option and found that all of the numeric values were "transformed" into text. This was done automatically by the driver I guess. I went back to the excel destination, but found that I could delete and recreate the sheet in the file. So I have 2 SQL tasks (1 to delete and 1 to create) before the data flow task. Try this Link http://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/ Thanks for your help! Paws27284

0 ·

question