October 12, 2011 at 2:41 pm
Hello,
I've scoured the boards for my question and found a lot of similar ones, but nothing quite the same. I developed an SSIS package that is deployed to a 64-bit SQL Server 2008R2 machine. When I run the package directly, it works fine. So I set up an SQL Agent job to run it. The SQL server Agent is using a proxy account. When I log into the server as the proxy account, I can connect to SSIS and manually run the package just fine. The problem come s in when I try to run it via the SQL Server Agent Job. I get the following error:
The Microsoft Jet database engine cannot open the file '<<Path to my Excel file>>'. It is already opened exclusively by another user, or you need permission to view its data.
I do have the "run in 32-bit" turned on. And I did set the package property in development mode. So I don't think it's a 32/64 bit issue. I even went so far as to change the SQL Agent job step from an SSIS package step to an Operating System step and manually run the 32-bit version of DTEXEC. But the Error still occurs when I run from SQL Agent.
Any ideas?
October 12, 2011 at 2:48 pm
Does the agent account (or the account running the agent job) have permissions to the directory? Is it on a file share that you only have access to?
October 12, 2011 at 7:01 pm
Yes, the agent account has permissions to the directory.
Additionally, when I log into the server as the Agent account and run the package from Management Studio, connected to SSIS (not via the SQL Server Agent) it runs just fine. So I know that the account that the Agent is using is capable of running the package. It only seems to be a problem when actually running the package from the Agent.
Scott
October 12, 2011 at 8:36 pm
Try moving the file local to the server. Put it on c:\import\ and see if it throws the same error. I think you are correct, it's not the 64 bit 32 bit problem since you are using the 32 bit exec. Just rule out a network problem by moving the problem local. Let the thread know what you find.
October 13, 2011 at 6:30 am
Well, that's bizarre. That seems to be the problem.
The source file exists on a subfolder of the C: drive. But it is placed there by a little desktop application. So the desktop application puts the file up on the share (\\ServerName\c$\MyFolder\MyFile.xls) and then updates the configuration table with the full path to the file. So the package is looking at the share. When I manually put the file in the share and update the configuration table with the local path (C:\MyFolder\MyFile.xls) the package runs successfully when launched from SQL Agent. The part that's confusing is why I can use the share name successfully when I log in as the Agent account and run the package. There should be no difference between my logging in as the agent account and the agent running the package via a proxy, right?
Scott
October 13, 2011 at 7:21 am
Try using an actual share \\SERVER\SHARE\ and give the appropriate permissions to the share. I think using \\SERVER\C$ could be the problem.
October 17, 2011 at 2:21 pm
That seems to be it. It took a little while to get the IT department to set up the share and the permissions. But the utility is using a newly created share (instead of the administrative share) and is functioning normally.
I still find it odd that I was able to successfully use the admin share when logged into the server as the SQL Agent account, but not when having the agent launch the package. But it's working, now. So thanks for all your help.
Scott
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply