Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 539

Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogPackageSuccess')
begin
print 'Dropping log.LogPackageSuccess stored procedure'
Drop Procedure [log].LogPackageSuccess
print 'Log.LogPackageSuccess stored procedure dropped'
end
print 'Creating log.LogPackageSuccess stored procedure'
go
Create Procedure [log].LogPackageSuccess
@PkgInstanceID int
As
update log.SSISPkgInstance
set EndDateTime = GetDate()
, Status = 'Success'
where PkgInstanceID = @PkgInstanceID
go
print 'Log.LogPackageSuccess stored procedure created.'
print ''
/* log.LogPackageFailure stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogPackageFailure')
begin
print 'Dropping log.LogPackageFailure stored procedure'
Drop Procedure [log].LogPackageFailure
print 'Log.LogPackageFailure stored procedure dropped'
end
print 'Creating log.LogPackageFailure stored procedure'
go