how to load data from sql table to excel

Question

Post reply

how to load data from sql table to excel

saidwarak01

SSCommitted

Points: 1577
More actions
March 29, 2010 at 2:47 pm

#138036

Hi Everyone,
I have a package where in I need to load the data from sql server to excel destination. The sql server table has 10 columns and among these columns few are of varchar(500). I used the data conversion transformation inside the data flow and converted these varchar(500) columns to Ntext and tried to load into excel but still package is failing.
Please let me know, if anyone has any solution to this issue. I already referred to few online resources and those are below but, I would like to have a better solution than the below mentioned in the link.
http://www.sqlservercentral.com/Forums/Topic349843-148-1.aspx.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Please post the text of your error message.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vidya_pande SSCertifiable Points: 5275 More actions · Answer 2

please help the guys here with good amount of details so that they can help you out.

Why are converting varchar to Ntext ?

saidwarak01 SSCommitted Points: 1577 More actions · Answer 3

Hi all,

I apologize for not posting the error message. I am attaching the error screen shot for your reference and also I am giving the specific error message below:

Excel destination Error [400] : An OLE DB ERROR OCCURRED Error Code : 0x80040E21

Excel destination Error [400] : Cannot create OLE DB accessor. Verify that the column metadata is valid

[DTS Pipeline] Error : component " Excel destination" (400) failed the pre execute phase and returned error code 0xC0202025.

Please find below the table scripts also:

create table test1(col1 varchar(500),col2 varchar(max))

insert into test1 values('Person1 ','Martin Luther King)

insert into test1 values('Person2 ','Mother Teresa')

insert into test1 values('Place1 ','New York')

insert into test1 values('Place

2','dkddkdkddkdkdkdkddkkkkkkkkkkkkkkkkkkkkkkkkkkdkjfkdjkfjdjfdfdkjfdkjf')

Please let me know if you need any other info.

Thanks in advance for all your help.

saidwarak01 SSCommitted Points: 1577 More actions · Answer 4

Hi,

Please let me know if anyone has any solution to this problem.... its urgent.

Thanks

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 5

Rt clk on the Excel destination, then select advanced and see the Input/Output column data types they should match.. if they dont just open up Excel destination In the column tab Unselect All Columns and then select them again and map them again.. see if that works.If it does not then recreate the Excel connection with a new Excel file and also try using text instead of varchar(max).. map the columns and then run the package... It seems that the metadata in Excel has not been updated.. Sometimes even after updating the Column metadata SSIS gives an error.. So create a new excel File with a diff name and use that..

Let me know if that helps