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

Select * from cfg.Applications
Select * from cfg.Packages
You will now use that information to execute the
cfg.AddSSISApplicationPackage stored procedure, building SSISApp1 in
the metadata of the SSISConfig database and assigning it Child1.dtsx and
Child2.dtsx —in that order. I use the T-SQL script shown in Listing A-9 to accom-
plish the mapping.
Listing A-9 . Coupling the Child1 and Child2 SSIS Packages to the SSISApp1 SSIS Ap-
plication
declare @ExecutionOrder int = 10
declare @ApplicationID int = 1
declare @PackageID int = 1
declare @ApplicationName varchar(255) = 'SSISApp1'
declare @PackageFolder varchar(255)
= 'F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'
If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID,
@PackageID, @ExecutionOrder
print @PackageName + ' added and wired to '
+ @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)