Weird dts problem

Question

Weird dts problem

Karthika Raman

SSCrazy

Points: 2520
More actions
August 10, 2006 at 2:06 pm

#61602

I have a dts package that is run by a scheduled job which was running successfully for several months and suddenly stopped working.

The dts package does the following: Truncates the table, does a data pump from a specific excel file to a table on sql server, deletes the excel file. The job does the following:

Step 1: Look to see if a file exists and if so, run the dts package. On success of this step the job goes to step 2.

Step2: Looks to see if any files still exist after the first step
Step3: Failure notification. To be triggered on failure of step 1 or 2.

The owner of the job is the Account that is used to run the SQLServiceAgent and in the SysAdmin server role.

The owner of the job is also in the SysAdmin server role.

Both the accounts are Windows Accounts.

The job runs at schedule time, finds the file, and runs the dts package. The first part of the dts package runs successfully – The table is cleared. However it is not doing the data pump part. There are no failure notifications. The job then goes to step two and sends a message that the file still exists. The dts task is set to output an error file which is not being updated, which tells that the task is not executed. If the dts package is run manually, then the package runs successfully.

Any help would be appreciated.

Thanks
KR

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Have you tried running manually on the server to make sure it is doing anything. Could your excel file be stuck? Turn the package logging on (package properties) so you can review in detail the package execution aftwards.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 2

Yes, it runs fine when manually run on the server and it is doing everything it is supposed to. I will turn on the logging and see what I get.

Thanks

KR

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 3

Whenever I've had this problem it has ALWAYS been a permission issue. Somewhere, by some unsuspecting person, something got changed. One server's SQL Agent permission may have been revoked on an other server, permissions on a specific table may have been reset, etc. Follow the permission tracks of every process component and convince yourself and the other DBAs that nothing has changed. I propose that in doing this you will stumble on the problem.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 4

Thank you all for your replies. I have been so busy, I am sorry it took me so long to post a reply - Yes, the permissions thing is my gut feeling too and I am going to follow that route.

Jon Russell Hall of Fame Points: 3048 More actions · Answer 5

I will repeat what Carlos suggested. For me too, when I have had this problem it is ALWAYS a permissions problem. Have you confirmed that the service account has the proper permission to the Excel file?

Karthika Raman SSCrazy Points: 2520 More actions · Answer 6

Thanks all,

It was a permission issue, but for an error logging file within the dts package. It was on a different directory, that someone had changed the permissions to.

KR