Solved

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

Posted on 2004-03-31
10
3,846 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:dapcom
  • 5
  • 2
10 Comments
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10723338
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.
0
 
LVL 1

Author Comment

by:dapcom
ID: 10724100
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
0
 
LVL 1

Author Comment

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

Dan
0
 
LVL 1

Author Comment

by:dapcom
ID: 10730915
Just in case my last comment is misleading: my problem is not resolved ;)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Expert Comment

by:monosodiumg
ID: 10733022
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.

0
 
LVL 1

Author Comment

by:dapcom
ID: 10766539

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
0
 
LVL 1

Author Comment

by:dapcom
ID: 10929143
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10966922
Closed, 250 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now