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

Else
print 'Log.SSISPkgInstance table already exists.'
print ''
/* log.LogStartOfPackage 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 = 'LogStartOfPackage')
begin
print 'Dropping log.LogStartOfPackage stored procedure'
Drop Procedure [log].LogStartOfPackage
print 'Log.LogStartOfPackage stored procedure dropped'
end
print 'Creating log.LogStartOfPackage stored procedure'
go
Create Procedure [log].LogStartOfPackage
@AppInstanceID int
,@AppPackageID int
As
declare @ErrMsg varchar(255)
Insert Into log.SSISPkgInstance
(AppInstanceID, AppPackageID, StartDateTime, Status)
Output inserted.PkgInstanceID
Values
(@AppInstanceID, @AppPackageID, GetDate(), 'Running')
go
print 'Log.SSISPkgInstance stored procedure created.'
print ''
/* log.LogPackageSuccess stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p