DTS job never execute

Last Post 12 May 2005 05:49 AM by cacrawford. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
LeCat
New Member
New Member

--
11 May 2005 11:57 PM
I have a DTS package that copies tables from DB2 to SQL Server. This DTS package is scheduled to run ones every day. The Job has two steps. The first is to delete the mssql table and the second is the DTS package import from the AS/400 DB2 to the table that was cleared in step one. If I right klick the DTS package and "execute package" it works... The import from DB2 to my mssql table does suceed. But the scheduled job doesnt. Only step one executes with sucess. On the Job status it says "Executing Job Step 2", and it says so all the time. Just like the the job trying and trying to execute step two but nothing happens.

Has this something to do with that if I just run the DTS package it runs under my account (that is an account in the AS/400 DB2), but if the job runs it runs under SQL Service Agents account (thats not in the AS/400) ? Or is it something else??

Please help me out.

(Sorry for my bad english)
cacrawford
New Member
New Member

--
12 May 2005 05:49 AM
There are a couple things that are different when you run a DTS package yourself vs. running it from an Agent job.

The first is the account that runs the package. As you suspected, the job runs under the SQL Server Agent's account when scheduled, but under your Windows account when you run it youself. However, the information that the DTS package uses to connect to SQL Server and DB2 would be stored in the Connection Task within the DTS package, so I'm not sure that this is the issue.

The other difference is that when you right-click the DTS package and run it, it runs from your box, not the server. When the SQL Agent runs the package, it runs on the server. I'm not familiar with DB2 connections - is there anything you need (a file, configurations settings, client libs, etc.) to connect to DB2 that might be on your PC, but not on the server?

If those don't help, can you run a trace or log on the DB2 server to see if the connection request is getting there and if the query is being run? Run a profiler on the SQL Server and see if everything looks clean there? Remote Destop or log onto the box with SQL Server, open up the DTS package and run it from there and see if it gives you any indication as to what is going on?

Hope this helps, good luck!
You are not authorized to post a reply.

Acceptable Use Policy