Please help - DTS problems in SQL 7 --> AS/400

I was wondering if any of you could shed some light on the following
problem?
We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
DR, Production, Development, Test) on our Datawarehousing Project.
They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
running version 4.5, last one upgraded to 5.1) for data refreshes,
using SP's and lots of DTS Packages.
Problem is that whenever we try to run DTS (using dtsrun.exe, works
fine in Designer Mode) against the upgraded AS/400 system on our Test
Server it gives out the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
string: up_processDTS Failed Error source: Microsoft OLE DB
Provider for SQL Server Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14); Provider
Error: 50000 (C350) Error string: up_processDTS Failed
Error source: Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
Provider Error: 0 (0) Error string: Package failed because Step
'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
Data Transformation Services (DTS) Package Help file: sqldts.hlp
Help context: 700. Process Exit Code 1. The step failed.
However, when I run the same DTS Packages from our Live, Dev and DR
machines, it works fine!
I have checked and double-checked the revision details, all four boxes
are the same. I have run the error message against MS KB and IBM,
nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
they only work from other NT boxes and not the Test Server which is
most bizarre. I've rebooted twice, nada.
Any help, suggestions, pointers etc. would be much appreciated.
Regards
ALIIt is only the one step that is failing
DTSStep_DTSExecuteSQLTask_1
Can you isolate this and run it. What does it do? If everything alse runs
fine and the ONLY difference is the version (5.1) then I would suggest that
as a possible cause
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0308070353.6f5ee16@.posting.google.com...
> I was wondering if any of you could shed some light on the following
> problem?
> We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
> DR, Production, Development, Test) on our Datawarehousing Project.
> They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
> running version 4.5, last one upgraded to 5.1) for data refreshes,
> using SP's and lots of DTS Packages.
> Problem is that whenever we try to run DTS (using dtsrun.exe, works
> fine in Designer Mode) against the upgraded AS/400 system on our Test
> Server it gives out the following error:
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
> string: up_processDTS Failed Error source: Microsoft OLE DB
> Provider for SQL Server Help file: Help context: 0
> Error Detail Records: Error: -2147217900 (80040E14); Provider
> Error: 50000 (C350) Error string: up_processDTS Failed
> Error source: Microsoft OLE DB Provider for SQL Server Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
> Provider Error: 0 (0) Error string: Package failed because Step
> 'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
> Data Transformation Services (DTS) Package Help file: sqldts.hlp
> Help context: 700. Process Exit Code 1. The step failed.
> However, when I run the same DTS Packages from our Live, Dev and DR
> machines, it works fine!
> I have checked and double-checked the revision details, all four boxes
> are the same. I have run the error message against MS KB and IBM,
> nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
> they only work from other NT boxes and not the Test Server which is
> most bizarre. I've rebooted twice, nada.
> Any help, suggestions, pointers etc. would be much appreciated.
> Regards
> ALI

plan for box upgrade

sql2k sp3
Im going to be moving sql from one box to a newer one
with better hardware today. This is my plan:
Restore a backup of Master and MSDB onto the new box.
Detach and attach the user db's.
Ive used detach and attach but never in this scenario.
Does anyone see a problem with this?
TIA, ChrisYour plan is perfect, only thing is that the restored
master database will still have entries for user
databases, even if they don't exist on new server yet. I
believe you can get rid of them by issuing drop database
command.
Then start attachig user databases, and then msdb.
hth.
>--Original Message--
>sql2k sp3
>Im going to be moving sql from one box to a newer one
>with better hardware today. This is my plan:
>Restore a backup of Master and MSDB onto the new box.
>Detach and attach the user db's.
>Ive used detach and attach but never in this scenario.
>Does anyone see a problem with this?
>TIA, Chris
>
>.
>|||My plan wasnt to detach and attach msdb at all. It was to
backup/ restore it. Will this still work?
>--Original Message--
>Your plan is perfect, only thing is that the restored
>master database will still have entries for user
>databases, even if they don't exist on new server yet. I
>believe you can get rid of them by issuing drop database
>command.
>Then start attachig user databases, and then msdb.
>
>hth.
>>--Original Message--
>>sql2k sp3
>>Im going to be moving sql from one box to a newer one
>>with better hardware today. This is my plan:
>>Restore a backup of Master and MSDB onto the new box.
>>Detach and attach the user db's.
>>Ive used detach and attach but never in this scenario.
>>Does anyone see a problem with this?
>>TIA, Chris
>>
>>.
>.
>|||you can backup/restore or attach/detach msdb database. or
just rename new mdf and ldf files for msdb on new server
(just for backup) and then copy original mdf/ldf files in
their place when the sql server is not running.
You can do same for master database as well, if you don't
want to use backup/restore.
hth.
>--Original Message--
>My plan wasnt to detach and attach msdb at all. It was to
>backup/ restore it. Will this still work?
>
>>--Original Message--
>>Your plan is perfect, only thing is that the restored
>>master database will still have entries for user
>>databases, even if they don't exist on new server yet. I
>>believe you can get rid of them by issuing drop database
>>command.
>>Then start attachig user databases, and then msdb.
>>
>>hth.
>>--Original Message--
>>sql2k sp3
>>Im going to be moving sql from one box to a newer one
>>with better hardware today. This is my plan:
>>Restore a backup of Master and MSDB onto the new box.
>>Detach and attach the user db's.
>>Ive used detach and attach but never in this scenario.
>>Does anyone see a problem with this?
>>TIA, Chris
>>
>>.
>>.
>.
>