dcsimg
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: OLE DB Error in Job Scheduler

  1. #1
    Join Date
    Nov 2006
    Posts
    42

    Question OLE DB Error in Job Scheduler

    I have an SSIS package set up that runs a stored procedure that populates a table, and then exports the table data to Excel. The package runs just fine if I execute it from Visual Studio. I have the package deployed to SQL Server (msdb table) with the protection level set to rely on the server and roles for access control.

    I created a scheduled job in SQL Server with one step -- to run the SSIS package. When I try to execute the job I get the error below. Again, this only happens when I try to run the package as a job.

    Error: 2009-02-11 10:27:21.24 Code: 0xC0202009 Source: Equipment_Counts_by_Zone Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x800... The package execution fa... The step failed.

    Can someone help my pinpoint the cause of this error and what to do to correct it?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,913
    Do you have Integration Service running on the server running SQL Server?

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Have to install ssis on sql server first.

  4. #4
    Join Date
    Nov 2006
    Posts
    42
    SSIS is installed. I should add that jobs that do not export data to Excel are running successfully. Does a Microsoft Jet driver need to be installed on the SQL Server?

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Where is excel file?

  6. #6
    Join Date
    Nov 2006
    Posts
    42
    The Excel file is on another server, and the connection maps to it with a UNC path. The package runs just fine if I execute it locally; it's only in the job scheduler that it fails.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    Ensure sql agent service account has permission to access that file.

  8. #8
    Join Date
    Nov 2006
    Posts
    42
    Is that different than the sqlsrvr account? Also, I should mention that I've tried running the job as SQL Server Agent and also using a proxy account with a credential. No dice either way.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Sql agent service account maybe different from sql engine service account. What are sql agent service account and proxy accout on the server?

  10. #10
    Join Date
    Nov 2006
    Posts
    42
    Actually I have the file folder permissions wide open. The "Everyone" group has full control for testing purposes, so I don't see how it would be a file permission issue.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    If you start sql agent with local system, sql job can't access remote files at all.

  12. #12
    Join Date
    Nov 2006
    Posts
    42
    The Agent is started with a domain SQL Server account.

  13. #13
    Join Date
    Sep 2002
    Posts
    5,938
    Is job owner a member of sysadmin role?

  14. #14
    Join Date
    Nov 2006
    Posts
    42
    No, but a proxy and a credential have been created for the job owner's account. Our company is very strict about giving out SA rights and even though that would be a simple solution, it's not an option at this time.

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Is proxy a domain account? You can enable ssis package logging to get details of failure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •