I need help with rolling back transaction

Hi,
I have the foll statements that are calling a dts package. I deliberately
misspelt the table name on the last update statement but my transaction
doesnot rollback why the DTS is suppose to rollback
SET NOCOUNT ON
BEGIN TRANSACTION
Select @.doc_no = str_inv_no from dbo.arctlfil_sql
WITH (TABLOCKX )
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("", , "dts")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
.......
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
SET str_inv_no = @.doc_no + 1
WHERE str_inv_no = @.doc_no
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
The DTS basically imports data from a textfile. The data is imported from
the text file even when I get the error
Server: Msg 208, Level 16, State 1, Line 64
Invalid object name 'dbo.fil_sql1' .
I want the entire procedure to be rolled back including the DTS. The
transaction is enabled on the DTS property.
Any ideas?Hi Chris
The DTS package is not being executed in the same context (it will have it's
own connection) and therefore will not be part of the transaction. Include
your update a a step in the package.
John
"Chris" wrote:

> Hi,
> I have the foll statements that are calling a dts package. I deliberately
> misspelt the table name on the last update statement but my transaction
> doesnot rollback why the DTS is suppose to rollback
>
>
> SET NOCOUNT ON
> BEGIN TRANSACTION
> Select @.doc_no = str_inv_no from dbo.arctlfil_sql
> WITH (TABLOCKX )
> EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
> IF @.hr <> 0
> BEGIN
> PRINT '*** Create Package object failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> -- DTSSQLServerStorageFlags :
> -- DTSSQLStgFlag_Default = 0
> -- DTSSQLStgFlag_UseTrustedConnection = 256
> EXEC @.hr = sp_OAMethod @.oPKG,
> 'LoadFromSQLServer("", , "dts")',
> NULL
> IF @.hr <> 0
> BEGIN
> PRINT '*** Load Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> .......
> EXEC @.hr = sp_OADestroy @.oPKG
> IF @.hr <> 0
> BEGIN
> PRINT '*** Destroy Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
>
> UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
> SET str_inv_no = @.doc_no + 1
> WHERE str_inv_no = @.doc_no
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION
> END
> COMMIT TRANSACTION
> The DTS basically imports data from a textfile. The data is imported from
> the text file even when I get the error
> Server: Msg 208, Level 16, State 1, Line 64
> Invalid object name 'dbo.fil_sql1' .
> I want the entire procedure to be rolled back including the DTS. The
> transaction is enabled on the DTS property.
> Any ideas?
>