July 10, 2003 at 4:45 pm
I have a problem with a few DTS packages that is scheduled to run through the SQL Server Agent. Some of these packages fail. However, they work fine if I open up these DTS package in the Designer and Run it from there. I'm running it directly on the Server logging in as "administrator", instead of on my PC. It also works if I open up the DTS package and run it from other PC's as well. I've left the Built-in Admin group alone in SQL Server so that it still has SA rights to the db. SQL Server Agent is using a Win2000 AD Domain Account with admin rights.
There are other DTS jobs that are scheduled that works fine. It's just a handful that doesn't seem to want to work through the SQL Agent. There isn't anything wrong with the DTS packages that are failing because it works just fine if I:
1. Right-Click and "Execute Package" through Enterprise Manager
OR
2. Execute it from DTS Designer
OR
3. Run it with DTSRun through the DOS prompt
OR
4. Run it with DTSRUNUI
Almost all the scheduled jobs look something like this:
DTSRun /~Z0x0515A9752C419EB2682B47EE176B74537 etc...
I've replaced this encrypted step with the uncrypted version that works (in #3) when I run it manually through DOS. The result is still the same. It fails. This is the only step in the Job. It's actually a simple job.
All of these failed packages are faily simple using common DTS tasks. About the only thing that is above "simple" is establishing an ODBC connection to a IBM DB2 database on the mainframe.
These jobs never work properly through SQL Server Agent. Once in a while, when I Right-Click and "Start Job" manually, it does work with "Succeed ...".
I've just upgraded from Win2000 SP3 to Win2000 SP4 in hopes of fixing it, but everything is still the same.
Anyone know what else I can do to troubleshoot this? I can't figure out why it's not working and I really need to get this fixed. It's becoming a daily routine to have to manually run these packages each morning.
July 11, 2003 at 7:34 am
I have come across this problem many times. I have never been able to determine the cause (probably a bug in the way security information are passed to the package through SQL Server Agent??) but a work around is to create your scheduled job manually and use the dtsrun command in your job step. Hope this helps.
Joseph
July 16, 2003 at 3:22 pm
If security is not so much of an issue, you can put the dtsrun command line into a .bat file and schedule that with the Windows scheduler. (I think I discovered that after running into the problem you are having - it's been awhile since I started, but I have a daily dtsrun using this method with no problems).
July 17, 2003 at 5:19 am
I have a bunch of DTS packages running nightly from SQL Agent that work fine but I have had problems in the past. Some problems I have had have been:
1. The DTS run command uses the DTS package GUID to find out which DTS package to run. If you make a change to a DTS package and save it, it is given a new GUID, so the job no longer runs the correct DTS package. Delete the job and recreate it.
2. Check the owner of the job has rights to run the DTS package and use the DTS connections if trusted connections are used.
3. Swear at it a bit.
4. In enterprise manager, right-click the job, select 'view job history' and look for the failure message. Post the message on this forum so we can have a look.
In my experience they either run first time with no problems or they are a real pain to sort out...
July 17, 2003 at 5:24 am
1) What is the error message in the history for each.
2) What account is SQL Server Agent set to run under?
3) Is there anything special that these have in common that the others do not? (Such as they access a share on the same remote machine, or they use a particular com object when running and the ohers do not.)
July 17, 2003 at 9:45 am
1. Error Message is "Failed (7/17/2003) 4:35:58 AM)". In View History:
The job failed. The Job was invoked by User 323S\SQLADM. The last step to run was step 1 (Import DB2 EMP_DATA).
Note, step 1 is the ONLY step. "Import DB2 EMP_DATA" is the name of the DTS package.
2. SQLAgent is using Win2K AD Domain Admin Account with local Admin rights "SQLADM".
3. Nothing special. Simple ETL from IBM DB2 to SQL Server. The complex ETL with scripts and external program calls works perfectly while these few simple ETL DTS Package just fails.
The hard part is troubleshooting these packages. Enterprise Manager has a terrible management area in troubleshooting this. The "Fail" message doesn't exactly help me figure out what it's complaining about.
All objects are owned by "dbo". It's our standard.
July 17, 2003 at 10:23 am
So the problem appears to be in the DTS package itself and not in the job calling the package.
To view the DTS log, right click the DTS package that has the problem and select 'Package logs', expand the latest version and double click the latest log. This will then give you a list of how each individual step went within the DTS package when it was last run. This should give you far more information about exactly what happened.
Good luck...
January 30, 2006 at 10:34 am
So any luck on fixing the problem? I'm experiencing the same issues with a few jobs that I have running. They all import data from a DB2 data base on an AS/400 but none of them perform any complex operations.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply