DTS Schedule Problem

#1

I have a SQL Server 2000 install on a Win 2K server and am runinng a simple Transfer DTS package which transfers 1 table (F4211) from a JD Edwards install on a DB2. If I run the package locally on the server by right clicking and selecting Execute Package the DTS runs fine and transfers the table

When I schedule the Job however the DTS fails to transfer data and throws no error. I have the Sql Security to System Account and I have also set the SQL Agent to System account. However when I create a DTS package it is created with my user name and dowmain (Braintree\tokeeffe). Does this user credential cause the failure? If so how to I reconcile all the account so that I can run this job as a schedule.

#2

This is a good article to start with

http://support.microsoft.com/?kbid=269074

Also I have had problems with moving data from World on DB2 to One World on
SQL Server using the IBM Client Access drivers. One thing I noted seemed to
be important was who installed the drivers. I seem to remember some
articles about it.

I would change the Agent account to a Domain Account certainly. Log in as
the Account and now fire the package

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"tim okeeffe" <tokeeffe (AT) symmons (DOT) com> wrote

Quote:

I have a SQL Server 2000 install on a Win 2K server and am runinng a
simple Transfer DTS package which transfers 1 table (F4211) from a JD

Edwards install on a DB2. If I run the package locally on the server by
right clicking and selecting Execute Package the DTS runs fine and transfers
the table.

Quote:

When I schedule the Job however the DTS fails to transfer data and throws
no error. I have the Sql Security to System Account and I have also set the

SQL Agent to System account. However when I create a DTS package it is
created with my user name and dowmain (Braintree\tokeeffe). Does this user
credential cause the failure? If so how to I reconcile all the account so
that I can run this job as a schedule.

#3

Nope you should be able to fire the package and let it take care of things

Easiest test?

Log in to the server as the Agent account
Execute the package manually.

You should get the errors interactively now.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"tim okeeffe" <tokeeffe (AT) symmons (DOT) com> wrote

Quote:

Allan, I was able to change the user on the SQL Agent to my user name and
create a new DTS which transfers 1 table F0116 to the SQL Server DB. This

works, when it is just one step. However when I place a query at the begin
and end of the DTS only the first query executes. I have it set up to test
if it can hanlde multiple steps which is what I need.

Quote:

so it does this

Create Table (Named: Begin) -> DTS Transfer of F0116 - > Create Table
(Named: End)

I only see the table beign and the F0116TMP table is not populated. Do I
need to mention each step in Job? I have workflow set to do each step.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS Schedule Problem

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS Schedule Problem - 03-01-2004 , 11:01 AM

Re: DTS Schedule Problem - 03-01-2004 , 11:28 AM

Re: DTS Schedule Problem - 03-02-2004 , 01:08 AM