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

Where name = 'custom')
begin
/* Create Schema statements must occur first in
a batch */
print ' - Creating custom schema'
Set @Sql = 'Create Schema custom'
Exec(@Sql)
print ' - Custom schema created'
end
Else
print ' - Custom Schema already exists.'
print ''
/* Drop the Custom.execute_catalog_package Stored
Procedure if it already exists */
print 'Custom.execute_catalog_package 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 = 'custom'
And p.name = 'execute_catalog_package')
begin
print ' - Dropping custom.execute_catalog_package'
Drop Procedure custom.execute_catalog_package
print ' - Custom.execute_catalog_package dropped'
end
/* Create the Custom.execute_catalog_package Stored
Procedure */
print ' - Creating custom.execute_catalog_package'
go
/*
Stored Procedure: custom.execute_catalog_package
Author: Andy Leonard
Date: 4 Mar 2012