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

/*Child2.dtsx */
set @PackageName = 'Child2.dtsx'
set @ExecutionOrder = 20
set @PackageID = 2
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)
One note about the T-SQL script shown in Listing A-9 . This is not the way I would
load this metadata into production (or even test) environments. I would not re-declare
the ApplicationName , PackageFolder , PackageName , ApplicationID ,
and PackageID variables; rather, I would reuse these values from the previous T-
SQL scripts. I alluded to this earlier when I mentioned we will use the Applica-
tionID and PackageID values later. I will provide a full T-SQL Metadata Load
script later in this appendix.
Retrieving SSIS Applications in T-SQL
We now have SSIS application metadata stored in the SSISConfig database. Awe-
some, now what? It's time to build a stored procedure to return the SSIS package