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