Trying to schedule a DTS with ODBC connection

[gbowe]

I have a DTS which accesses our AS400 to pull information into a SQL database.  Running the DTS works fine, but when I schedule the DTS in the SQL Server Agent it fails everytime without any good failure message. Is there some type of security setting I need to implement to allow the scheduler to run the ODBC DSN.

 

[gpatton]

Has anyone had any luck with this issue? I am using DTS in SQL Server 2000 to pull data from our AS/400 using IBM's client Access ODBC driver. If I execute the package manually (even if I am actually on the server itself) it works perfectly. As soon as I try to schedule it with SQLAGent, it dies halfway through. It doesn't appear to be a security context issue because it will commit 16,000 of the 45,000 records to the table before it dies. I placed a SQL trace on it and it just dies, no error message at all.

Any ideas?

Thanks

 

[foxdev]

gpatton, I think your issue is different than the OP (which was almost certainly security related).

When it "dies", do you get any SQL Server messages at all? If not, what is the symptom of death (i.e., hangs; bad smell; etc)? Can you check the SQL Server Agent log (check the Details box) and see what the output is? Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[gpatton]

It leaves no messages in any of the logs (Win 2000 event logs, SQLAgent, DTS). It appears as far as SQLAgent is concerned, as long at it kicks off the DTSRUN statement, it is successful because SQLagent always indicates job success.

The DTSRUN.exe process just stops. I have to go into task manager and end it. It typically leaves a lock on the DB as well.

I am thinking bug...

Thanks,
Greg

 

[foxdev]

Pretty odd. I don't have SQLS handy (I'm having to work in Sybase), but the SQL Server Agent messages I'm referring to are not the "error logs" per se, but rather the long list of text that you see when you go to SQL Server Agent Job History (?), then click Show Details, and there's a scrollable pane that shows (usually) tons of status messages being reported back from the DTS job (things such as "1000 records processed...2000 records processed" and so forth).

When you run the job, can you first start Profiler and watch its activity to see if there are any clues there? Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[gpatton]

The most detail the SQL Server Agent Job History gives me is this:

The step did not generate any output. Process Exit Code 0. The step succeeded.

I am batching the records 1000 at a time. Profiler just indicates it inserted 1000 rows, then another 1000 etc. It then just stops. No more output is generated from the trace. It usually stops around 16,000 rows for this table, but that varies depending on the table I am trying to process.

Greg