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 19:13:47 -0000

You implement a transaction in the package

In the package propertes

Package Properties | Advanced

set it to use transactions

In each task | Workflow properties

Join transaction if present
Rollback transaction on failure

Yes the "Insert one row at a time" will take longer 

-- 
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:ucPwldDEEHA.1272@TK2MSFTNGP11.phx.gbl...
> 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

  • =?windows-1252?Q?Re=3A_Bill_Dillon_=96_A_Gentleman_and_a_Scholar_=28Just_?= =?windows-1252?Q
    ...  Despite the package being ... our friends at the USPS managed to misplace ... How many people have we actually done a transaction with that we’re ... Prior to this trade I didn’t knowBill. ...
    (rec.games.pinball)
  • Re: Nvidia sucks, sucks, sucks !
    ... Most of the rest of the people using their drivers aren't moaning ... us could get the livna install working. ... --> Populating transaction set with selected packages. ... Transaction Check Error: package kernel-2.6.18-1.2798.fc6 is already ...
    (Fedora)
  • DTS Error: Unable to enlist in the transaction
    ... I'm looking to use DTS to delete and reload some volatile data from ... Running the DTS package designer locally, ... - I've tried this with both Windows Authentication and SQL Server ... select "Join Transaction If Present" and "Rollback ...
    (microsoft.public.sqlserver.dts)
  • Yum Erasure Behavoir With Pending Update
    ... I just erased a package while there was an update awaiting that ... remove the package and dependencies. ... --> Populating transaction set with selected packages. ... ---> Downloading header for gimp to pack into transaction set. ...
    (Fedora)
  • Re: Nvidia sucks, sucks, sucks !
    ... Most of the rest of the people using their drivers aren't moaning ... get the livna install working. ... --> Populating transaction set with selected packages. ... Transaction Check Error: package kernel-2.6.18-1.2798.fc6 is already ...
    (Fedora)