Link to home
Start Free TrialLog in
Avatar of dapcom
dapcomFlag for Switzerland

asked on

DTS Package running on a server, but not the other one

Hello gurus,

I have a problem with a DTS Package I created.

The package has 4 steps (running 1 after another on Success) that imports data from a distant SQLServer and send it to a DB2 (OS390) database.

When I run the package from my desktop, it runs OK.
This is a SQLServer 2000 SP3a (hotfix 0819).

I saved the package on the production server (wich is a MSDE with same level. Here the steps that are inserting data into DB2 fails:
Exec log
-----------------------------
Step '2_Import Distinct YearMonths' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified.
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
-------------------------------

Exceptions log:
-------------------------------
Execution Started: 3/25/2004 6:07:44 PM
Error at Destination for Row number 1. Errors encountered so far in this task: 1.  
Error code: 80004005  2004|1|
2004|1|

Execution Completed: 3/25/2004 6:07:44 PM
--------------------------------

When I searched the net, I found people having the same error, but there was always a more descriptive error, like duplicate or something. Here nothing except that the problem is in Destination (DB2)

We see here the data to be imported : 2004/1, wich are Integers.

The table is emptied before the import (I checked, this step works). The package currently contains the logon/password for both db, (SQL authentication for SQL Server).

I tested the same package on a third computer with SQLServerSP3 (no Hotfixes). This one fails at the same point, but it gives more details:

Exec log:
--------------------------------
Step '2_Import Distinct YearMonths' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 2 ('MO', DBTYPE_I4), status 9:  Permission denied.) (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 1 ('YR', DBTYPE_I4), status 9:  Permission denied.) (Microsoft OLE DB Provider for ODBC Drivers (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
--------------------------------

Exceptions log:
--------------------------------
Error at Destination for Row number 1. Errors encountered so far in this task: 1.  

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Insert error, column 2 ('MO', DBTYPE_I4), status 9:  Permission denied.
Error Help File:sqldts80.hlp
Error Help Context ID:30702
 

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Insert error, column 1 ('YR', DBTYPE_I4), status 9:  Permission denied.
Error Help File:sqldts80.hlp
Error Help Context ID:30702
 

Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description:Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Error Help File:
Error Help Context ID:0
  2004|1|
2004|1|
-------------------------------------

Permission denied on columns.
Again, thje package runs from my sqlserver with the same users saved in the connections.

I tried with and without the 'Fast Load'option. I read something about allow identity insert, but it doesn't apply here I think (but I tried). ther's only 2 columns wich are just Integers.

So, that's it. I'd be gratefull for any thought of what could I try. There must be a difference (or is it MSDE?). I don't have any more PC to test on,
and I don't like to install sp or fixes on the production server if not necessary. (when I installed the last fix, I lest for a time the connection to it,
somehow the protols in net config where all removed, it's always hours of searching to know wich to install... always feel stupid when reading ms instructions...)

Thanks for reading this, and more thanks in advance for any help you might give me.

Dan
Avatar of monosodiumg
monosodiumg

Whne you say you run the package from your desktop, do you mean the destination is still DB2?

Is the locale on your desktop the same as that of the production server?

Can you try running it using an SQL server DB as the destination? Does it work then?
Check also the connection settings (esp user & pw) for the DB2 connection.
Avatar of dapcom

ASKER

by running on my desktop, I mean using for example Enterprise reporting installed on my PC, connecting to the NT Server where I saved the package, and where only MSDE is installed.(production Server, where the package eventually must run), opening the package (stored on the server) and clicking play.

Or with command-line on my PC:DTSRun -SServer -Uuser -Ppwd -N"PackageName" (where Server is the prod server)

This updates the db2 tables with the data from the distant sql database.

when I go to the server, and type the same command (dtsrun, as there is no ER) it give the bad result (but the package does run).

Eventually, the package will be run from an aspx page; that already works when the webserer runs from my PC, but not from the server. It's not exactly the same IIS I guess, but as I have the same difference with DTSRun, I discarded this as the source of the problem.

The connection to DB2 is good: it's working from my PC, and when running on the server, it does empty the DB2 tables (1st step). Only the insert fails. I know the user has right to insert in the table (it's my loogn, and the package works (delete, and insert) from my PC).

Thanks for your help,

Dan
Avatar of dapcom

ASKER

BTW, I changed the package to insert into its own tables, and it work fine from my PC and 'from' the server.

Dan
Avatar of dapcom

ASKER

Just in case my last comment is misleading: my problem is not resolved ;)
Not sure what to try next. I'd want to see more error info.
There a good article here on trapping error info from DTS packages: http://www.revealnet.com/newsletter-v4/0603_E.htm
That might shed some light.

Avatar of dapcom

ASKER


It would seem the problem come in fact from the DB2 connect version. My PC had 7.1.3 and it worked, the server 7.1.10 and it failed. I just installed 7.1.11 on my PC and I now get the same error:
Error Description:Insert error, column 2 ('MO', DBTYPE_I4), status 9:  Permission denied.

Now I have a little more to dig into...

Daniel
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial