I need the transformation below to ROLLBACK if ERROR occurs Inserting Into
and/or Updating DIM table. Right now, it's COMMITTING some records into the
DIM table when the transformation process abended. I have a sample code I'm
trying unsuccessfully......but need to know where I incorporate this for it
to work.

Example:
/*
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END
*/

Thanks!

/************************************************** *******************************
* Date: 10-05-2004
* Description: Stored Procedure Created to load data into dwcontractDim
* Updated:
************************************************** ********************************/
--Exec up_loadDataContractDim

CREATE procedure up_loadDataContractDim

AS

SET NOCOUNT ON

--Declare variables

DECLARE @C1 smallint,
@C2 int,
@C3 datetime,
@C4 datetime,
@C5 datetime,
@C6 char(1),
@C7 char(1),
@C8 varchar(100),
@C9 char(3),
@C10 varchar(10),
@C11 char(1),
@C12 varchar(100),
@C13 char(3),
@C14 varchar(100),
@C15 char(1),
@C16 varchar(100),
@C17 int,
@C18 int,
@C19 int,
@C20 char(2),
@C21 varchar(25),
@C22 char(3),
@C23 varchar(30),
@C24 char(4),
@C25 varchar(30),
@C26 int,
@C27 char(45),
@C28 int,
@C29 int,
@C30 int,
@C31 int,
@C32 char(1),
@C33 char(1),
@C34 char(1),
@C35 char(1),
@C36 datetime,
@C37 char(1),
@c38 varchar(255),
@strProcName varchar(25), -- holds this procedure's name.
@intError INT, -- declare our error code variable. This will be used
@strErrDescription varchar(200),
@packageName varchar(100),
@transformationName varchar(50),
@packageSource varchar(10),
@packageTarget varchar(10),
@sourceTable varchar(50),
@targetTable varchar(50),
@packageid UNIQUEIDENTIFIER,
@recInserted NUMERIC,
@exStatus varchar(10)

DECLARE @MyCursor CURSOR
-- initialize variables
SELECT @intError = @@Error
SELECT @strProcName = 'up_loadDataContractDim'
SELECT @strErrDescription = ''
SELECT @packageName='CMSContractDimTrans'
SELECT @packageSource='stg'
SELECT @packageTarget='dw'
SELECT @recInserted=0

IF @intError = 0
BEGIN
SELECT @transformationName='Insert records from STG to dwContractDim'
SET @MyCursor = CURSOR FAST_FORWARD
FOR

SELECT
companyNum,
contractNum,
contractStartDate,
contractEndDate,
contractExpirationDate,
rightOfFirstRefusalFlag,
contractExtensionType,
contractExtensionName,
demandVolCode,
rateSchedule,
contractTypeCode,
contractTypeName,
serviceTypeCode,
serviceTypeName,
contractSubTypeCode,
contractSubTypeName,
releasingContractNum,
originalReleaseContractNum,
parcelNum,
regionCode,
regionName,
teamCode,
teamName,
accountRepCode,
accountRepName,
utilityNum,
utilityName,
utilityCompanyLENum,
shipperLegalEntityNum,
shipperContactNum,
payerLegalEntityNum,
fercArea,
negotiatedRateFlag,
mdqTermStatus,
transportStorageInd,
GETDATE() AS recordCreatedDate,
'A' AS recordStatusCode
--(CASE recordStatusCode WHEN 'N' THEN 'A' WHEN 'U' THEN 'U' END)

FROM [NNGCO\thasan].stgContractDim
WHERE recordStatusCode = 'N' OR recordStatusCode = 'U'


OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @C1,@C2, @C3, @C4,@C5, @C6,@C7,@C8, @C9, @C10,
@C11,@C12,@C13,@C14, @C15, @C16,@C17, @C18,@C19,@C20, @C21, @C22,
@C23,@C24,@C25,@C26, @C27, @C28,@C29, @C30,@C31,@C32, @C33, @C34,
@C35,@C36,@C37
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @recInserted=@recInserted+1

INSERT INTO [NNGCO\thasan].[ContractDim]
(companyNum,
contractNum,
contractStartDate,
contractEndDate,
contractExpirationDate,
rightOfFirstRefusalFlag,
contractExtensionType,
contractExtensionName,
demandVolCode,
rateSchedule,
contractTypeCode,
contractTypeName,
serviceTypeCode,
serviceTypeName,
contractSubTypeCode,
contractSubTypeName,
releasingContractNum,
originalReleaseContractNum,
parcelNum,
regionCode,
regionName,
teamCode,
teamName,
accountRepCode,
accountRepName,
utilityNum,
utilityName,
utilityCompanyLENum,
shipperLegalEntityNum,
shipperContactNum,
payerLegalEntityNum,
fercArea,
negotiatedRateFlag,
mdqTermStatus,
transportStorageInd,
recordCreatedDate,
recordStatusCode)
VALUES (
@C1,@C2,@C3,@C4,@C5,@C6,@C7,@C8,@C9,@C10,@C11,@C12 ,@C13,@C14,@C15,@C16,@C17,@C18,@C19,@C20,@C21,@C22 ,@C23,@C24,@C25,@C26,@C27,@C28,@C29,@C30,@C31,@C32 ,@C33,@C34,@C35,@C36,@C37)

SELECT @intError = @@Error





IF @intError <> 0
BEGIN
--write to error table
BEGIN TRAN tranPackageErrors

SELECT @c38 = cast(@c1 as varchar(2))+ ' '+cast(@c2 as
varchar(4))+ ' '+ cast(@c3 as varchar(10))+ ' '+ cast(@c4 as varchar(10))+ '
'+ cast(@c5 as varchar(10))+ ' '+@c6 + ' '+ @c7
+'
'+ @c8 +' '+ @c9+' '+ @c10+' '+ @c11+' '+ @c12+' '+ @c13+' '+ @c14+' '+ @c15+
' '+cast(@c16 as varchar(4))+ ' '+cast(@c17 as varchar(4))+ ' '+cast(@c18 as
varchar(4))+ ' '+@c19 + ' '+ @c20+ ' '+@c21 + ' '+ @c22+ ' '+@c23 + ' '+
@c24+ ' '+cast(@c25 as varchar(4))+ ' '+ @c26+ ' '+cast(@c27 as varchar(4))+
' '+cast(@c28 as varchar(4))+ ' '+cast(@c29 as varchar(4))+ ' '+cast(@c30 as
varchar(4))+ ' '+@c31 + ' '+ @c32+ ' '+@c33+ ' '+@c34+ ' '+@c35

SELECT @strErrDescription=description FROM
[master].[dbo].sysmessages where error=@intError
INSERT INTO [NNGCO\rchinta].[GAS_ODS_PackageErrors]
(packagename, transformationName, errorNumber, errorDescription,
executionDate, errorrecord)
VALUES (@packageName,@transformationName, @intError,
@strErrDescription, getdate(), @c38)
COMMIT TRAN tranPackageErrors
BREAK
END

FETCH NEXT FROM @MyCursor
INTO @C1,@C2, @C3, @C4,@C5, @C6,@C7,@C8,@C9,@C10, @C11,@C12,@C13,@C14,
@C15, @C16,@C17, @C18,@C19,@C20,@C21, @C22,@C23,@C24,@C25, @C26, @C27,@C28,
@C29,@C30,@C31, @C32, @C33, @C34,@C35,@C36,@C37
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
END

IF @intError= 0
BEGIN
SELECT @packageid = packageId FROM
[NNGCO\rchinta].GAS_ODS_PackageExecutionStatus WHERE packageName =
@packageName AND executionStatus IS NULL
--Update endtime
UPDATE [NNGCO\rchinta].GAS_ODS_PackageExecutionStatus SET
endTime=getdate(), executionStatus ='SUCCESS'
WHERE packageId=@packageid

--select @recInserted=count(*) from [NNGCO\thasan].[stgcontractDim]

--update time elapsed
UPDATE [NNGCO\rchinta].GAS_ODS_PackageExecutionStatus SET
elapsedtime=datediff(ss,starttime, endtime), rowsProcessed=@recInserted
WHERE packageId=@packageid
END


IF @intError = 0
BEGIN

SELECT @recInserted=0
SELECT @packageid = newid()
INSERT INTO [NNGCO\rchinta].GAS_ODS_PackageExecutionStatus (packageid,
packagename, starttime,executeAction)
VALUES (@packageid, @packageName, getdate(), 'UPDATE')

SELECT @transformationName='Update DW recordStatusCode'

UPDATE [NNGCO\thasan].ContractDim SET
recordStatusCode = 'I'
FROM
ContractDim c1
WHERE
c1.recordCreatedDate <>
(Select MAX(c2.recordCreatedDate)
FROM [NNGCO\thasan].ContractDim c2 WHERE c2.recordStatusCode = 'A' AND
c2.contractNum = c1.contractNum)


--IF @@ROWCOUNT = 0
--SELECT @strErrDescription= 'No rows updated in stgContractDim'

SELECT @recInserted=@@rowcount
SELECT @intError = @@Error
--IF @intError <> 0
--BEGIN
--SELECT @strErrDescription= 'Error updating information in stgContractDim'
--END
END



IF @intError= 0
BEGIN
SELECT @exStatus='SUCCESS'
if @recInserted=0
SELECT @exStatus='FAILED'
--Update endtime
UPDATE [NNGCO\rchinta].GAS_ODS_PackageExecutionStatus SET
endTime=getdate(), executionStatus =@exStatus, rowsProcessed=@recInserted
WHERE packageId=@packageid
--update time elapsed
UPDATE [NNGCO\rchinta].GAS_ODS_PackageExecutionStatus SET
elapsedtime=datediff(ss,starttime, endtime)
WHERE packageId=@packageid
END


IF @intError <> 0
BEGIN
--write to error table
SELECT @strErrDescription=description FROM [master].[dbo].sysmessages
where error=@intError

BEGIN TRAN tranPackageErrors

INSERT INTO [NNGCO\rchinta].[GAS_ODS_PackageErrors]
(packagename, transformationName, errorNumber, errorDescription,
executionDate)
VALUES (@packageName,@transformationName, @intError, @strErrDescription,
getdate())
COMMIT TRAN tranPackageErrors

END


GO