Re: dts transaction

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: pauli (paulixml_at_yahoo.com)
Date: 06/26/04 

Date: Sat, 26 Jun 2004 14:39:40 -0700

Thanks, I am new to DTS, I was wondering if you would do it differently
than what I have.

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:elZmRs4WEHA.1764@TK2MSFTNGP10.phx.gbl...
> If you are using a proc to do the rest then why not do everything in a
proc
> and use a transaction over all this work?
>
>
>
>
> "pauli" <paulixml@yahoo.com> wrote in message
> news:eQ2cl13WEHA.2544@TK2MSFTNGP10.phx.gbl...
> > Hi thanks for the reply.
> >
> > I see the transaction in the package properties, but I do not see the "
> Join
> > Transaction if present or Rollback... " in the workflow properties?
> >
> > Also, please advise if what I am doing is correct. I am trying to set
up
> a
> > package that will import about 10 tables from access database. Theres
one
> > main table called person with about 9 other tables that include
education
> > and employment.. We will received these databases from about 15
partners.
> > Heres what I am doing in my package.
> > 1. I import the the access database into a temp database that I have
> > created in SQL Server.
> > 2. I added a column name person_accessID into the person table in the
> > actual database. This is necessary because there is a person_ID
identity
> > field. I need to update the other tables in the temp database with the
> new
> > person_id when they are inserted.
> > 3. I insert the person table first into the database. I run a stored
> > procedure that select the person_ID and person_accessID from the person
> > table and updates the other tables in the temp database with this new ID
> > 4. Then I run a transform data task to insert the rest of the data
into
> > the database.
> >
> > Is that the correct way to do this?
> >
> > Thanks for you help.
> >
> > Pauli
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:utwSZ70WEHA.2840@TK2MSFTNGP11.phx.gbl...
> > > You need to enable transactions in the package (Package Properties)
> > > In ech step that you want to enroll in the transaction you need to set
> > >
> > > "Join Transaction if present"
> > > "Rollback Transaction on failure"
> > >
> > > in the workflow properties
> > >
> > > --
> > > --
> > >
> > > 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
> > >
> > >
> > > "news.microsoft.com" <paulixml@yahoo.com> wrote in message
> > > news:ODUvXlxWEHA.1152@TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > I am trying to create a dts package to import about 10 tables from
> > access
> > > to
> > > > SQL server using the DTS import and export wizard. I saved the dts
> > > package
> > > > and tried to add a work flow to make sure the person table is
inserted
> > > first
> > > > before the other table. But how can I roll back the entire
> > transformation
> > > > if one of the table failed?
> > > >
> > > > thanks
> > > >
> > > > Pauli
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: dts transaction
    ... If I had 90% of my stuff in a stored proc and the rest of the data ... migration/manipulation was not excessive and could be done in TSQL then I ... stored>>> procedure that select the person_ID and person_accessID from the person>>> table and updates the other tables in the temp database with this new ID ...
    (microsoft.public.sqlserver.dts)
  • Re: Another Concurrency Issue!!!!
    ... > allocating in blocks, using tables with identities, etc. ... > Columnist, SQL Server Professional ... I have a proc which returns a value after some DML operations on ... >> TIA ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005
    ... The SQL Server implementation is not just Stored Procs, ... it works very tightly in proc with the SQL Server Engine(most ... Now with all that I have said T-SQL is not going away and is still a needed ... >> the CLR is now supported in stored procedures and triggers. ...
    (borland.public.delphi.non-technical)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)
  • Re: Stored proc timeout issue
    ... The program Opens the proc and times out after 2 minutes. ... proc from sql server mgmt studio, ... I began diagnosing the problem by commenting out various lines of code in the stored proc ... One thing that remained changed was the command timeout value. ...
    (borland.public.delphi.database.ado)