Post reply

how to load data from sql table to excel

  • 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://qa.sqlservercentral.com/Forums/Topic349843-148-1.aspx.

    Thanks in advance.

  • March 30, 2010 at 12:52 am

    #1141975

    Please post the text of your error message.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • March 30, 2010 at 1:31 am

    #1141987

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

    Why are converting varchar to Ntext ?

  • March 30, 2010 at 9:21 am

    #1142340

    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.

  • March 30, 2010 at 1:31 pm

    #1142631

    Hi,

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

    Thanks

  • March 30, 2010 at 1:54 pm

    #1142657

    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

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

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