Post reply

SQL 2005 export to Excel - specifying OleDB connection manager

  • May 17, 2010 at 2:34 pm

    #140870

    I'm using SSIS to export a Sql server 2005 table to Microsoft Excel 2003. Trying to export the table to excel that is on my desktop from a different server within the same network.

    My Datasource reader and character map are setup correctly, I'm getting the following error within the Excel Destination Manager:

    I have excel file path as c:\temp\filename.xls

    excel version as microsoft excel 97 - 2005

    error I'm getting is: The microsoft jet database engine could not find the object 'excel destination'. Make sure the object exists and that you spell it's name and path name correctly.

    Does anyone know what my problem could be? Should I specify the name of my desktop? Does the userid I'm logged into on the server have to have permission to the path where the excel file is to be located or should the sql server agent running the ssis package have permission to the path? Am I specifying the path correctly?

    Thank you, I need to get this working...

    Diane

  • May 17, 2010 at 2:44 pm

    #1168462

    Its hard to tell with that error message.. you definitely need to provide the complete path.. In the Excel connection Manager, browse and then select the correct file..

    Alternative Approach:

    Try to use Import/export wizard from Management studio and then save it as a package before you click finish. Open up the package in BIDS and then compare it with the package you have developed to find the error..

  • May 18, 2010 at 8:35 am

    #1168861

    Thank you for your reply,

    I tried your second suggestion to create it in the input/export wizard and save it before it creates the ssis package.

    I don't even get as far as saving the ssis package.. it bombs at the Destination screen where I enter the Microsoft Excel as the destination and the path where I want the file.

    I have the path as: \\servername\c:\temp\Filename.xls

    When I hit enter it fails with an error: Database or object is read only.

    Under the Security in Event errors it says: Logon/Logoff

    eventID: 538

    userid: domain\servername (where sql is)

    computer: my computer name

    Does this mean that I'm not giving the servername permission to the file path? If so can you tell me exactly which I need to give permission to, the directory or or the file?

    Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

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