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
>

Scheduled DTS fails, manual works. Help!

Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.

My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.

I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!It's likely that the account that is setup for SQL server to use against your off-site resource does not have the credentials required. When you run the package manually, it's actually running as you. When it's run as a job, it's run under the server's account. I would check to make sure credentials are setup properly for your server.|||I'd just change the credentials used by the job step that runs the DTS package so that it actually DOES run as you, making the conflict a non-issue!

Note that this neatly side-steps a much larger problem. User accounts should not be required to make scheduled jobs run. You ought to have a "service account" with sufficient privledges to perform the task. This is a cornerstone concept for enterprise job scheduling.

-PatP|||Thanks for the info, I'll check that out. I do have one other job that runs 100% locally, and that one fails as well. We do have a load of jobs that run various times during the day, btu these are the only two that fail. I've checked the schedules, and the databases being used i nthe DTS aren't being accessed in any way when these are supposed to be running.|||Your "other" job may or may not have any of the same symptoms as the DTS you originally posted regarding. Does the "other" job run when scheduled as a job but not when run locally? If not, then that is a seperate issue...