October 21, 2008 at 1:03 pm
Hello,
I have a DTS package, it's been working for years, I just noticed it stops working month ago, I checked the log history, there is no failed log, just stopped from Aug30.
What could be the cause? Where should I look into?
Thanks.
October 21, 2008 at 1:05 pm
Wow, a lot of questions here....
Is this run by a job? If so, what errors are you seeing there?
Do you have a log for the DTS package?
What happens when you run it manually step by step?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 21, 2008 at 1:10 pm
I checked the log history, no any record for showing either success or fail since Aug 30.
October 21, 2008 at 1:14 pm
When you say the log record is that the dts package log or for the job that is running the package? Have you checked to see if the job that runs the package is enabled (right click > look to see if says enable or disable)?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 21, 2008 at 1:14 pm
What happens when you run it manually step by step?
Good remind!
The error message says a local folder doesn't exist, but the folder is there.
However I am not sure what role the DTS package is used to access the folder? How do I find out?
Thanks for your help.
October 21, 2008 at 1:16 pm
If it is being run by a job the job account should have access to that folder. Could very well be a permissions issue. I would verify that the account that the job runs under (probably the SQL Server Agent Service account) has permissions to that folder. If so, then I would get temporary permissions for your account so that you can continue your step by step run in troubleshooting the package.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 21, 2008 at 1:43 pm
I have just verified the user that the job is run under has Modify/Read/Write... except for Full Control to that folder.
I am running the DTS again to see if a "failed" history will be created, the DTS takes very long time to finish, I am still waiting for it. There are five steps in the DTS package, when I manually run each step in the package, the first step throws the folder permission problem.
October 21, 2008 at 1:56 pm
I still don't understand:
There are 5 steps in the master DTS package, each step is a sub DTS package, when I tried to run each step in the master DTS manually , step 1 failed immediately for the folder permission problem; then I tried to run the whole master DTS to see what happened, it is still running after 30 minutes, I can see step1-3 failed, step 4's job history has no failed record. step 5 waiting 4 to finish.
The question is:
Why there is no log for the Aug30-Today? I would assume there should be at least "fail" info in the package's log history.
Since the user used to run the job has the necessary permission to the folder, why it throws the "folder doesn't exist or permission not right" error?
Thanks.
October 21, 2008 at 2:23 pm
You've just revealed some new information. So there's a master package with 5 Execute Package tasks. Do each of the child packages have logging enabled? Have you checked the package logs for them?
Greg
October 21, 2008 at 2:32 pm
Each of the sub DTS has no history reported since Aug 30.
Before Aug 30, everything is fine.
October 21, 2008 at 2:41 pm
I suggest you make sure logging is enabled in the first child package and step through it like you did with the master package. If the step that is failing is a Transform Data task, you should enable logging for that particular task, also.
Greg
October 21, 2008 at 2:58 pm
All the child packages are logging-enabled, log shows there was no any problem until Aug 30.
October 21, 2008 at 3:47 pm
What are you seeing for errors when you step through the child packages? I understand that there is no logging but you should be able to see the errors when running manually.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 21, 2008 at 4:52 pm
Step 1 prompted a "folder not exist" permission problem, all the following steps show different error messages caused by step 1.
This is weird, there got to be something wrong on Aug 29 but I just have no any clue.
October 22, 2008 at 7:17 am
At this point you have to start digging into the permissions on that folder and ensuring that the account running the process (as well as yourself) have access to it. My guess is that you will find the problem there. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply