"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
> >
> >
>
>

"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
>
>

"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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"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
> > >
> > >
> >
> >
>
>

"pauli" <paulixml@yahoo.com> wrote in message
news:%23%23zFbY8WEHA.3120@TK2MSFTNGP12.phx.gbl...
> 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>