Execution Patterns - SQL Server Integration Services Design Patterns - page 63

begin
Set NoCount On
Select p.FolderName, p.ProjectName,
p.PackageName, ap.ExecutionOrder
From custom.ApplicationPackage ap
Join custom.Package p
On p.PackageID = ap.PackageID
Join custom.Application a
On a.ApplicationID = ap.ApplicationID
Where a.ApplicationName = @ApplicationName
And ap.ApplicationPackageEnabled = 1
Order By ap.ExecutionOrder
end
go
print ' - Custom.GetApplicationPackages Stored Procedure
created.'
print ''
/* Build custom.AddApplication stored procedure */
print 'Custom.AddApplication'
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 = 'custom'
And p.name = 'AddApplication')
begin
/* If custom.AddApplication stored procedure
exists, drop it */
print ' - Dropping custom.AddApplication Stored
Procedure'
Drop Procedure custom.AddApplication
print ' - custom.AddApplication Stored Procedure
dropped'
end
print ' - Creating custom.AddApplication Stored Procedure'