MS SQL Server DTS - Problem using transactions in a package

Author

Problem using transactions in a package

Jaime Stuardo

2005-09-21, 1:26 pm

Hi all....

I created a package that has 5 steps. 1 Transform Data Task and the others
Execute SQL Task. All of them inserts data in the data base.

In package properties I checked "Use transactions" and "Commit on successful
package completion" checkboxes.

When I run the package, and if, for example, the last step fails, all
changes made by the previous steps are committed. When this situation occurs,
I have to manually delete all data that was inserted by the previous steps.

Is there a way to commit the changes if and only if all steps have completed
succesfully?

I have SQL Server 2000.

Thanks a lot in advance
Jaime

Allan Mitchell

2005-09-21, 8:24 pm

Yep

Have you seen this?

http://msdn.microsoft.com/library/d...ddf_tx_0tmb.asp

"Jaime Stuardo" < JaimeStuardo@discuss
ions.microsoft.com> wrote in
message news:DDE80ADE-7BB7-4964-9C49- 7C45E97325B6@microso
ft.com:

> Hi all....
>
> I created a package that has 5 steps. 1 Transform Data Task and the
> others
> Execute SQL Task. All of them inserts data in the data base.
>
> In package properties I checked "Use transactions" and "Commit on
> successful
> package completion" checkboxes.
>
> When I run the package, and if, for example, the last step fails, all
> changes made by the previous steps are committed. When this situation
> occurs,
> I have to manually delete all data that was inserted by the previous
> steps.
>
> Is there a way to commit the changes if and only if all steps have
> completed
> succesfully?
>
> I have SQL Server 2000.
>
> Thanks a lot in advance
> Jaime

Jaime Stuardo

2005-09-22, 8:24 pm

Thanks Allan for the answer.

I followed all directions in the page, but I get the error :

"Connection [CONNECTION] for Task [TASK] does not support joining
distributed transactions or failed when attempting to join"

This happens for all steps, for example, a simple INSERT into a SQL Server
database. This is it:

INSERT INTO TARJETA (TAR_ESTADO, TAR_FECSOL, TAR_VIGATEC, TAR_ENTREGA,
SUC_ID, TAR_DIRECCION)
SELECT 'A', GETDATE(), 'P', 'S', S.SUC_ID, 'SUCURSAL : ' + S.SUC_NOMBRE
FROM TMP_PRODUCCION_NUEVA
T, OFICINA O, SUCURSAL S
WHERE T.CODOFIEMI = O.OFI_COD
AND O.SUC_ID = S.SUC_ID

DECLARE @TAR_ID INT
SET @TAR_ID = @@IDENTITY

INSERT INTO CONTRATANTE (CON_RUT, CON_DV, CON_NOMBRES, CON_APELL_PAT,
CON_APELL_MAT)
SELECT NUMID, DVID, NOMTER, APEPATER, APEMATER
FROM TMP_PRODUCCION_NUEVA
T
WHERE NOT EXISTS(SELECT 1
FROM CONTRATANTE C
WHERE C.CON_RUT = T.NUMID)

INSERT INTO CON_X_TAR (CON_RUT, TAR_ID)
SELECT NUMID, @TAR_ID
FROM TMP_PRODUCCION_NUEVA
T
WHERE EXISTS(SELECT 1
FROM CONTRATANTE C
WHERE C.CON_RUT = T.NUMID)

GO

Jaime

"Allan Mitchell" wrote:

> Yep
>
>
> Have you seen this?
>
> http://msdn.microsoft.com/library/d...ddf_tx_0tmb.asp
>
>
> "Jaime Stuardo" < JaimeStuardo@discuss
ions.microsoft.com> wrote in
> message news:DDE80ADE-7BB7-4964-9C49- 7C45E97325B6@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-22, 8:24 pm

And what is that CONNECTION ?

"Jaime Stuardo" < JaimeStuardo@discuss
ions.microsoft.com> wrote in
message news:DE7EE85B-7542-46DB-B4C5- C1350E0BA8C5@microso
ft.com:
[color=darkred]
> Thanks Allan for the answer.
>
> I followed all directions in the page, but I get the error :
>
> "Connection [CONNECTION] for Task [TASK] does not support joining
> distributed transactions or failed when attempting to join"
>
> This happens for all steps, for example, a simple INSERT into a SQL
> Server
> database. This is it:
>
> INSERT INTO TARJETA (TAR_ESTADO, TAR_FECSOL, TAR_VIGATEC, TAR_ENTREGA,
> SUC_ID, TAR_DIRECCION)
> SELECT 'A', GETDATE(), 'P', 'S', S.SUC_ID, 'SUCURSAL : ' + S.SUC_NOMBRE
> FROM TMP_PRODUCCION_NUEVA
T, OFICINA O, SUCURSAL S
> WHERE T.CODOFIEMI = O.OFI_COD
> AND O.SUC_ID = S.SUC_ID
>
> DECLARE @TAR_ID INT
> SET @TAR_ID = @@IDENTITY
>
> INSERT INTO CONTRATANTE (CON_RUT, CON_DV, CON_NOMBRES, CON_APELL_PAT,
> CON_APELL_MAT)
> SELECT NUMID, DVID, NOMTER, APEPATER, APEMATER
> FROM TMP_PRODUCCION_NUEVA
T
> WHERE NOT EXISTS(SELECT 1
> FROM CONTRATANTE C
> WHERE C.CON_RUT = T.NUMID)
>
> INSERT INTO CON_X_TAR (CON_RUT, TAR_ID)
> SELECT NUMID, @TAR_ID
> FROM TMP_PRODUCCION_NUEVA
T
> WHERE EXISTS(SELECT 1
> FROM CONTRATANTE C
> WHERE C.CON_RUT = T.NUMID)
>
>
> GO
>
> Jaime
>
> "Allan Mitchell" wrote:
>
>
> http://msdn.microsoft.com/library/d...y/en-us/dtssql/
> dts_addf_tx_0tmb.asp
>
> all
> situation
>

Jaime Stuardo

2005-09-23, 7:23 am

A connection to the same server, using OLEDB provider for SQL Server.

When I don't use transactions all package works but with the problem you
know if any step fails. That means the connection isn't the problem and
since it is pointing to itself, that is, a SQL Server 2000 database, I think
it should support transactions.

Jaime

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:uDW%23Z38vFHA.3556@TK2MSFTNGP12.phx.gbl...
> And what is that CONNECTION ?
>
> "Jaime Stuardo" < JaimeStuardo@discuss
ions.microsoft.com> wrote in message
> news:DE7EE85B-7542-46DB-B4C5- C1350E0BA8C5@microso
ft.com:
>
>

Allan Mitchell

2005-09-24, 7:23 am

MSDTC started?

"Jaime Stuardo" <jstuardo@manquehue.net> wrote in message
news:uJ4wrCDwFHA.1132@TK2MSFTNGP10.phx.gbl:
[color=darkred]
> A connection to the same server, using OLEDB provider for SQL Server.
>
> When I don't use transactions all package works but with the problem you
> know if any step fails. That means the connection isn't the problem and
> since it is pointing to itself, that is, a SQL Server 2000 database, I
> think
> it should support transactions.
>
> Jaime
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:uDW%23Z38vFHA.3556@TK2MSFTNGP12.phx.gbl...
>
> message
>
> TAR_ENTREGA,
> S.SUC_NOMBRE
>
>
> http://msdn.microsoft.com/library/d...y/en-us/dtssql/
>
>
> the
>
>
>
>
> previous
> have
>

Jaime Stuardo

2005-09-28, 3:24 am

Yes... all SQL Server services are running

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:%23NheYQOwFHA.1032@TK2MSFTNGP12.phx.gbl...
> MSDTC started?
>
> "Jaime Stuardo" <jstuardo@manquehue.net> wrote in message
> news:uJ4wrCDwFHA.1132@TK2MSFTNGP10.phx.gbl:
>
>

Sponsored Links