Scheduled DTS not running

I created a DTS Local Package that should run on a specified time (ex. 9:00 AM) using EnterPrise Manager on my PC.

It is basically copying a file from our AS400 to the SQL server. It works fine if you execute it but the scheduler does not run at the specified time.

Am I missing anything here?

TIFOriginally posted by ARPRINCE
I created a DTS Local Package that should run on a specified time (ex. 9:00 AM) using EnterPrise Manager on my PC.

It is basically copying a file from our AS400 to the SQL server. It works fine if you execute it but the scheduler does not run at the specified time.

Am I missing anything here?

TIF

chk if SQL Agent is started|||Yup. The Agent is running. When I checked the jobs, I see an "X" on the DTS job I created and says that it failed. However, I can't see a log that gives me the exact reason why it failed.|||Check your Agent service account has permission on AS 400 server!|||When I configured my DTS, I entered the AS400 user credentials so I figured this is enough since when I execute my DTS, in works just fine. It's only when I use the schedule that it doesn't work.

How do you check anagent service account permission on a AS400? Sorry, I'm relatively new using this DTS thing.

Thanks|||can you just check sql agent error, see what it says!!
Agent right-click--Display error log.|||Don't forget that when you interactivly run a DTS package, it runs under your credentials. When SQLAgent runs it, it runs with the permissions given to SQLAgent. Maybe you have permissions to access the AS400 but SQLAgent does not??|||Originally posted by tomh53
Don't forget that when you interactivly run a DTS package, it runs under your credentials. When SQLAgent runs it, it runs with the permissions given to SQLAgent. Maybe you have permissions to access the AS400 but SQLAgent does not??

I see the error log but it does not show my any logs for yesterday and today. As a matter of fact, the last log date is 2/15/2004.|||Probably Agent configured to clear all jobhistory logs..
In anycase as tom said, look into SQLAgent service account has permission to connect AS400. When you run it manually it takes your credentials..when it runs under SqlAgent it looks for SqlAgent service account.|||Thanks for the TIPs. I will look into it more thoroughly.

Scheduled DTS issue - copying of data

Hi all,
I have a problem.
I have a scheduled DTS package that runs every week that copies data
only from staging server to production into a table. When the job is
finished it states that all is successful. When I check the row count
for this table there is a discrepancy with what exists in prod to that
which exists in staging. In prod there is about 200,000 records
less. If I run the DTS job manually...then all is correct - rowcounts
are the same. When I run it from the schedule it does not.
Does anyone know why this is occurring and how to resolve the issue?
Thanks.Hi
You don't say which version of SQL Server you are running on, or if this is
a straight forward import/export or how you are handling errors.
Has data been added after the import has occurred?
John
"woohoo30@.hotmail.com" wrote:
> Hi all,
> I have a problem.
>
> I have a scheduled DTS package that runs every week that copies data
> only from staging server to production into a table. When the job is
> finished it states that all is successful. When I check the row count
> for this table there is a discrepancy with what exists in prod to that
> which exists in staging. In prod there is about 200,000 records
> less. If I run the DTS job manually...then all is correct - rowcounts
> are the same. When I run it from the schedule it does not.
> Does anyone know why this is occurring and how to resolve the issue?
> Thanks.
>|||On Nov 1, 12:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You don't say which version of SQL Server you are running on, or if this is
> a straight forward import/export or how you are handling errors.
> Has data been added after the import has occurred?
> John
>
>
Hi John,
1st it truncates the table and then it does a transform of data on
staging to production server.
there are no errors...it says successful.
It inserts about 2million rows into the table...there is a discrepancy
of 200,000.
I don't understand why it's occurring and how to fix this issue.
woohoo30|||Hi
If you use a linked server to the staging server and use a INSERT... SELECT
statement from the live server does this bring all the rows you expect?
Does the live server have a primary key? Does the staged data have this key,
if not how many rows are unique for the primary key columns?
John
"woohoo30@.hotmail.com" wrote:
> On Nov 1, 12:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > You don't say which version of SQL Server you are running on, or if this is
> > a straight forward import/export or how you are handling errors.
> >
> > Has data been added after the import has occurred?
> >
> > John
> >
> >
> >
>
> Hi John,
> 1st it truncates the table and then it does a transform of data on
> staging to production server.
> there are no errors...it says successful.
> It inserts about 2million rows into the table...there is a discrepancy
> of 200,000.
> I don't understand why it's occurring and how to fix this issue.
> woohoo30
>