Re: package incomplete after error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 17:27:48 -0000

OK

Because by default the whole of the source is inserted into the destination
in 1 batch. A PK violation will abort the batch.
Have a look at the properties of the pump and change the sizes of the batche
(Fetch and commit sizes) 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Andrew Gale" <AGale10495@aol.com> wrote in message
news:%237khp$CEEHA.1272@TK2MSFTNGP11.phx.gbl...
> i have a simple package with 2 steps:
>
> 1. truncate table
> 2. simple transform data task - copy rows into table from text file
>
> if there are no duplicate rows that violate the primary key, the package
> runs fine - table is truncated, rows inserted.
>
> but after truncating the table, if the transform data task finds a
duplicate
> row that violates the primary key, it rolls back the rows it had inserted
> and doesn't insert any more.  so i'm left with an empty table from the
> truncation step.  the transform data task max error count is 10, so i
don't
> know why it's doing this.
>
> i tried "rollback trans. on failure" and "fail package on step failure" to
> no avail -   i don't think the step is failing.
> i even tried disabling transactions in the package props, and using
> transactions in VB (where i want to run the package from) - but
transactions
> don't seem to work in VB when using DTS packages.
>
> any ideas?... sql server 2k, winxp
>
> --
> andrew gale
> agale10495@aol.com
>
>

Relevant Pages

  • Re: package incomplete after error
    ... instead of continuing the package execution, ... > in 1 batch. ... A PK violation will abort the batch. ... truncate table ...
    (microsoft.public.sqlserver.dts)
  • Re: [input devices] more about mice
    ... >>>The post processing of this file is done with a batch (convert the ... >programs the batch is sufficient. ... I could only see 2x 25Mhz crystals and was wondering how ... >automatically run at the speed indicated on the package. ...
    (alt.lang.asm)
  • package incomplete after error
    ... truncate table ... if there are no duplicate rows that violate the primary key, the package ... if the transform data task finds a duplicate ... transactions in VB - but transactions ...
    (microsoft.public.sqlserver.dts)
  • Speaking of Crabapple Jelly
    ... I found a package of crabapple juice in my freezer, ... I made half a batch, using half a package of pectin?yeah, yeah, I know; ... I mumbled about being able to get a couple "good" jars from the ...
    (rec.food.preserving)
  • Re: apt-get errors out religiously while processing at command
    ... I uninstalled the 'at' package, ... > You may want to try reinstalling it now and see if things continue to ... Delayed job execution and batch processing ...
    (Debian-User)