SQL 2008: agent job error in opening excel files using DTS package

Question

I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an Excel file on a remote server like \\SQL2000S2\C$\directory\someExcelFile.xls. The SQL server and agent on SQL2008S1 are both running under same service account (Domain\SQL2008S1_service) and has full permissions on \\SQL2000S2\C$\directory. I even granted Domain\SQL2008S1$ (machine name) full access to \\SQL2000S2\C$\directory\

When I open the DTS package on SQL2008S1 and execute it manually, it runs perfectly fine. But when I schedule it as job, the job reports:

Executed as user: Domain\SQL2008S1_service. ...p_DTSExecutePackageTask_5 DTSRun OnError: DTSStep_DTSExecutePackageTask_5, Error = -2147467259 (80004005) Error string: DTSStep_DTSExecuteSQLTask_2: The Microsoft Jet database engine cannot open the file '\\SQL2000S2\C$\directory\someExcelFile.xls'. It is already opened exclusively by another user, or you need permission to view its data.

I am 100% sure that the files are not open and I thought I have granted enough rights to the SQL agent/SQL service account.

Any help please?

Answer 1

Have you granted the account Domain\SQL2008S1_service admin rights to the server SQL2000S2? You will need to do so in order to use the C$ network share.