Solved

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

Posted on 2004-03-31
10
3,859 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:dapcom
ID: 10730915
Just in case my last comment is misleading: my problem is not resolved ;)
0
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 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