I have a VB program that moves data down from a SQL Server to an Access
database. Each time the DTS runs the WHER Statements may change on the
selects where it is pulling data for different customers.
The entire package will excute without hitch sometimes and fail at other
times although never on same company and never on same place but it is always
the same error. I have attached below the error from one step that failed as
recorded in the log file... any idea on how to prevent this would be great
Thanks
Adam

----
Step 'Copy Data from tblDataByZip3 to tblCarByZip3 Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. (Microsoft JET Database Engine (80004005): Could not update;
currently locked by user 'Admin' on machine 'ACAIN'.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Hi Adam,
This error looks like permission problem...
Try to see if the user that you are using in vb can execute the pkg from the
enterprise manager without issue.
Rgds
JFB

"Adam Cain" <AdamCain (AT) discussions (DOT) microsoft.com> wrote

I'm pretty sure it's not a security issue...

I have run it from VB under my login (I have database owner rights) and
under the domain administrator and sa login as well. I originally developed
it under my login using the Enterprise Manager and then just savesd it as a
VB module so I could tweak the where clauses.

Plus I'll run it once and it fail and run it again and it won't fail. It is
a 12 step DTS job copying stuff down and it fails on different steps but
always the same error. Each step is doing the same type of stuff - copying
data from a table limited with a where clause to a single cutomer down to an
Access database that we then give to the customer.

The log sample was simply from the last time it crashed on me.

"JFB" wrote:

Ok Adam then you probably have issues with identities.
Try to turned on the option "Enable Identity Insert" on the Data
transformation task.
Rgds
JFB

"Adam Cain" <AdamCain (AT) discussions (DOT) microsoft.com> wrote

Went to each task and set .AllowIdentityInserts = True All of them had been
set to false for previous attempts.
Ran once fine. Crashed next time. Ran three times then crashed one more time.

Looks like that may not be it either but I do appreciate all the help. It is
really boggling me because the vb program loops through a list of customers
if that customers database doesn't exist it copies over the template and then
runs this dts. On a dts error it halts at a msgbox (giving me time to inspect
the database for possible clues) then deletes itself. that way I can rerun it
and it picks up right where it left off each time. it seems like if it
crashes once on a customer it would everytime and yet next time i do it it
starts up with that one and runs it fine. I have actually considered on crash
making it try to repat itself up tp 5 times before really giving up. I would
rather fix the error though so I do appreciate all your help...

Adam

"JFB" wrote:

Ok.. maybe this link will help you to understand and clear your problem ...
http://www.databasejournal.com/sqlet...le.php/1428511
Rgds
JFB

"Adam Cain" <AdamCain (AT) discussions (DOT) microsoft.com> wrote

Article didn't help. All the tables in question are already in 3NF.
Let me know if you think of anything else. This DTS is supposed to go into
production at the end of the month with a bunch of other stuff and I'm at a
complete loss right now.
Thanks

"JFB" wrote:

Got it figured out. At least I seem to have it fixed. The DTS was created by
SQL Server with two connections to Access. Step 1 used the first. Step 2 the
second. # the 1st again...
I changed it all to just one connection and it has run about 100 time now
without incidence.

"Adam Cain" wrote: