Configuration - SQL Server Integration Services Design Patterns - page 432

EXEC
[SSISDB].[catalog].[set_execution_parameter_value]
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = @FileNameParameter,
@parameter_value = @NameValue
-- Start the package execution
EXEC [SSISDB].[catalog].[start_execution]
@exec_id
-- Return the execution ID
SELECT N'Started package execution '
+ CONVERT(nvarchar(20), @exec_id)
-- Mark the file as processed
DECLARE @UpdateSql nvarchar(1024) = N'UPDATE
[dbo].[PackageConfiguration] SET processed = 1
WHERE id = ' + CONVERT(nvarchar(20), @Id)
EXEC sp_sqlexec @UpdateSql
SET @count = @count + 1
END
Conclusion
This chapter has covered some of the usage patterns for the new parameter model, as
well as some dynamic configuration scenarios. Although the configuration patterns and
best practices that were commonly used in SQL 2005 and 2008 continue to work in the
latest version of SSIS, most users will see a benefit in migrating to the new model. The
clarity of the parameter model was designed to help everyone involved with an SSIS
solution life cycle, from those who develop the packages to those who deploy and
schedule them.