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

declare @tbl table (AppID int)
If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)
begin
Insert Into cfg.Applications
(ApplicationName)
Output inserted.ApplicationID into @tbl
Values (@ApplicationName)
end
Else
insert into @tbl
(AppID)
(Select ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName)
Select @AppID = AppID from @tbl
go
print 'Cfg.AddSSISApplication stored procedure created.'
print ''
Note the cfg.AddSSISApplication stored procedure returns an integer value
that represents the identity column— ApplicationID —from the
cfg.Applications table. Add an SSIS application to the table using the following
T-SQL in Listing A-7 .
Listing A-7 . Adding an SSIS Application
declare @ApplicationName varchar(255) = 'SSISApp1'
declare @ApplicationID int
/* Add the SSIS First Application */
If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)