| Author |
Topic |
|
JayW
Starting Member
6 Posts |
 Posted - 2011-01-13 : 19:07:21
|
| I want to delete the source Excel file once the data copy has completed sucessfully. I created a task the runs an ActiveX script. If I execute the script by itself, it works fine. When I run the package, the task gets a "Permission denied" error. I hope someone can help. Here is the script:Function Main()Dim FSO, sFileName' Get the name of the file from the global variable "ImportFileName"sFilename = DTSGlobalVariables.Parent.Connections("Connection 1").DataSourceSet FSO = CreateObject("Scripting.FileSystemObject")' Check for file and return appropriate resultIf FSO.FileExists(sFilename) ThenFSO.DeleteFile sFileNameMain = DTSTaskExecResult_SuccessEnd IfSet FSO = NothingEnd Function |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-13 : 20:10:06
|
Does the Agent service account have access to the directory where the file is located?That would be your "permission denied" error. The agent service account doesn't have permissions to the directory.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
JayW
Starting Member
6 Posts |
Posted - 2011-01-14 : 10:49:07
|
| Thank you for your response. Where do I find the log on used by the SQL agent? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-14 : 12:27:47
|
Check the servers user list. It is a system/domain account usually, and if properly created would have SQLAGENT or AgentService in it's nameAlso you can right click the Server Agent and check the properties there.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
JayW
Starting Member
6 Posts |
Posted - 2011-01-14 : 17:19:24
|
| I changed SQLSERVERAGENT logon from "System account" to domain administrator, and I still get the same error message. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-14 : 17:34:23
|
you need to grant access to the ID which runs the agent job folder where the file is physically located.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
JayW
Starting Member
6 Posts |
Posted - 2011-01-14 : 17:57:52
|
| In an attempt to eliminate all access issues, I ran the job directly from the SQL server logged on as domain administrator. The Excel file I wish to delete is located on this server. Shouldn't this work? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-14 : 18:30:14
|
It depends on what account is running the SQL Server Agent. Just because you are logged on as an admin, and start the job, doesn't mean those credentials are passed. Usually, the agent runs under a service account which is not a domain admin.Check under the services in your computer information for SQL Server Agent and see which account is responsible for starting and running the agent. You will have to stop and restart services for the change to take effect. Whatever service account is running the agent, that account needs to have permissions to access the folder.Again--just because you personally have access, and are logged in as an system admin on the box, doesn't mean the Agent account will have access.You should be able to identify the login used to run the agent by looking under services for the O/S. Whatever it is, you have to grant it access to the folder location where the folder resides.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
JayW
Starting Member
6 Posts |
Posted - 2011-01-17 : 13:13:26
|
| Again, thank you for your time.I changed SQLSERVERAGENT to logon as myself. I gave myself FULL control to the folder that contains the file I wish to delete. Did not work. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-17 : 13:28:37
|
It boils down to one of very few potential issues. The account trying to access the folder does not have permission.The folder/file does not exist.One of the above must be causing the permissions error. There isn't some other magic button to push here...have you verified that sFileName in your script is correctly populated?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
JayW
Starting Member
6 Posts |
Posted - 2011-01-20 : 11:39:22
|
| I resolved the challenge. DTS wouldn't delete the file in Connection1 while the connection was still open. There is a workflow option to "Close connection on completion". Once this was selected, the package completed successfully.Thanks for you assistance. |
|
|
|
|
Delete Excel Source