Errors Exporting SQL to Excel xls

Question

Errors Exporting SQL to Excel xls

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

This error

component "Excel Destination" (16)" wrote 65535 rows.

is too much of a coincidence. You must be writing to a pre-2007 version of Excel, which can support a maximum of 65535 rows.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Initially I had it set to an xlsx version.

Then I got an error that I must specify an xls.

I have since changed the file type and connection manager but I have another error.

I belive that I have to select another provider.

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

For .xlsx you need to use the ACE OLE DB provider. Normally this is automatically selected when you choose Excel 2007 in the Excel Connection Manager.

Also make sure you are running the package in 32-bit, as the ACE and JET provider don't really support 64 bit.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Koen Verbeeck (10/28/2011)
For .xlsx you need to use the ACE OLE DB provider. Normally this is automatically selected when you choose Excel 2007 in the Excel Connection Manager.
Also make sure you are running the package in 32-bit, as the ACE and JET provider don't really support 64 bit.

Thanks.

I realized that I need the provider and I had to set the Advanced Extended Properties to Excel 12.0.

I was able to export the Data to Excel successfully. This export on contained the first 4 columns of many that I needed to export to this particular WorkSheet.

But when I tried a SQL Data Source that contained all of the Column, a large number, I get the following error Starting with the first column for every column.

Column "Quoted" cannot convert between unicode and non-unicode string datatypes. The Columns in SQL are non-unicode.

I tried changing the Data Types to non-unicode and I also tried the Data Conversion Task but obviously I'm missing something.

I could cast the data types in SQL but what is the best approach and does anyone have a good example with a SQL Source and an Excel Destination?

Any help would be greatly appreciated.

Thanks guys for your help. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 247183 More actions · Answer 5

The data conversion task should do the job - are you aware that it creates a new variable (so you need to change your column mappings to use what the DC task has converted)?

Or are you sure that you can't just use a CSV file instead? Much easier.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Phil,

Thanks for getting back to me again.

I want back to my original small column set but since I last successfully ran it, I had added the CONVEERT function on the Date Columns.

I removed the the CONVERT, ran the package and it completed without error.

If I run the package with all of the columns (100+) I get the unicode error on every column.

It would be nice if I could output to a CSV File but the Workbook is huge.

I have to populate several Worksheets.

The Workbook also contains several tabs that consist of Pivot Tables.

There is a lot of information in the Report Header and the columns heading usually don't start until row 7.

Do you have any good links for the Data Conversion Task (with screen shots)?

Thanks for your help. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I have found that the Data Conversion Task is need for each column that use an Alias on.

An Alias is needed on all most all of the columns to meet the requirements.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I have resolved the issue.

The only things that I do not like is that by default it gives the Alias Name Copy of Column Name to every Column.

You also have to specify the Data Conversion Source when mapping each column.

I had to populate 8 Worksheets so it was a little time consuming. :w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/