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

begin
print 'Adding ' + @ApplicationName
exec cfg.AddSSISApplication @ApplicationName,
@ApplicationID output
print @ApplicationName + ' added.'
end
Else
begin
Select @ApplicationID = ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName
print @ApplicationName + ' already exists in the
Framework.'
end
print ''
The script in Listing A-7 uses the cfg.AddSSISApplication stored proced-
ure to add the SSISApp1 SSIS application to the cfg.Applications table in the
SSISConfig database.
A Note About Relationships
An SSIS application is a collection of SSIS packages that execute in a prescribed order,
so it is pretty obvious that the relationship between an SSIS application and SSIS pack-
ages is one-to-many. What may not be as obvious is the relationship between SSIS
packages and SSIS applications. Herein is a key benefit of choosing patterns-based de-
velopment: code reusability, specifically in reference to the SSIS package code. Con-
sider the archive file pattern from the end of Chapter 7 on flat file design patterns. In an
enterprise that loads data from dozens or hundreds of flat file sources, this package
may be called many times by different SSIS applications. So the relationship between
SSIS packages and SSIS applications is also one-to-many. If you do the set math, these
relationships combine to create a many-to-many relationship between the applications'
and packages' tables. This means you need a bridge or resolver table between them to
create mappings between SSIS applications and SSIS packages.
I call this table cfg.AppPackages . Listing A-8 contains the T-SQL script that
creates cfg.AppPackages and a stored procedure with which it is loaded.