Re: package incomplete after error

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

From: Andrew Gale (AGale10495_at_aol.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 12:59:20 -0500

i set "insert batch size"=1, and it works now - but performance was greatly
reduced - it takes a LOT longer now.

ok, so that won't do. instead of continuing the package execution, how do i
rollback the package altogether if there's an error?

step1. truncate
step2. transform data

i want both steps rolled back to previous state before package began
(including undoing the truncation)....?? 

-- 
andrew
"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:%23YvzjLDEEHA.2404@TK2MSFTNGP11.phx.gbl...
> 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
    ... A PK violation will abort the batch. ... truncate table ... > if there are no duplicate rows that violate the primary key, the package> runs fine - table is truncated, rows inserted. ...
    (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)