Configuration - SQL Server Integration Services Design Patterns - page 430

Dynamic Package Executions
In this approach, you will use the same table from Listing 17-5 , but instead of reading
the configuration values with an SSIS package, you'll use T-SQL to create dynamic
package executions on the SSIS Catalog. The code in Listing 17-10 implements the
following steps:
1. Declare script variables. Note that in a real-world script, these values
would be set through parameters or from an external source.
2. Read the list of files to process from the PackageConfigura-
tion table, and store the results in a table variable ( @FileList ).
3. Loop through the list of files. For each file, the code will do the fol-
lowing:
a. Retrieve the ID and parameter values from the table variable.
b. Create a new SSIS Catalog package execution.
c. Set the parameter Directory and FileName parameter values.
d. Start the execution.
e. Update the PackageConfiguration table to mark that the file
has been processed.
Listing 17-10 . Dynamic Package Execution Script
DECLARE @FolderName NVARCHAR(50) = N'ExecutionDemo'
DECLARE @ProjectName NVARCHAR(50) = N'ETL'
DECLARE @DirectoryParameter NVARCHAR(50) = N'Directory'
DECLARE @FileNameParameter NVARCHAR(50) = N'FileName'
DECLARE @PackageName NVARCHAR(100) = N'LoadCustomers.dtsx'
DECLARE @FileList TABLE
(
RowNum smallint,
Id int,
Directory nvarchar(255),
Name nvarchar(255)
)