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

,PackageFolder)
Output inserted.PackageID Into @tbl
Values (@PackageName, @PackageFolder)
end
Else
insert into @tbl
(PkgID)
(Select PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
Select @PkgID = PkgID From @tbl
go
print 'Cfg.AddSSISPackage stored procedure created.'
print ''
Note the cfg.AddSSISPackage stored procedure returns an integer value that
represents the identity column— PackageID —from the cfg.Packages table. You
will use this integer value later. Once this stored procedure is in place, you can use the
T-SQL script in Listing A-5 to add the packages in the project.
Listing A-5 . Adding the Packages to the cfg.Packages Table
/* Variable Declaration */
declare @PackageFolder varchar(255)
= F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\F:\Andy\Projects\
PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'
declare @PackageID int
/* Add the Child1.dtsx SSIS Package*/
If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName