-
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?
-
Do you have Integration Service running on the server running SQL Server?
-
Have to install ssis on sql server first.
-
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?
-
-
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.
-
Ensure sql agent service account has permission to access that file.
-
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.
-
Sql agent service account maybe different from sql engine service account. What are sql agent service account and proxy accout on the server?
-
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.
-
If you start sql agent with local system, sql job can't access remote files at all.
-
The Agent is started with a domain SQL Server account.
-
Is job owner a member of sysadmin role?
-
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.
-
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
-
Forum Rules
|
|