I am working on a BI Project to load a daily snapshot of data into a BI database.
A date parameter drives the whole ETL where a daily Snapshot of data loads.
At first glance we wanted to use a SSIS project parameter to load the date where all the related SSIS packages use the Date project parameter. We stored the ETL load date in a configuration table and set the Date Project Parameter according to this date. I used the code below to set the project parameter at the start of the ETL.
Exec [SSISDB].[catalog].[set_object_parameter_value]
@object_type=20,
@parameter_name=N’ParameterName’,
@project_name=N’ProjectName’,
@folder_name=N’Folder’,
@value_type=V,
@parameter_value=20140104
(Also see the Microsoft Documentation for the stored procedure)
When testing the ETL from the SSIS catalog the process were not picking up the changed project parameter. Only when executing the package for the second time did the date set in the previous run register in the packages.
This resulted in a change of strategy in terms of using a Project Parameter. Instead we opted for a Package Parameter and added the date parameter on each package. In the SSIS Control package we used Execute Package Tasks to call the child packages and made use of the Parameter Bindings on the tasks to set the Date Package Parameter of each child package.
This worked perfectly and the Date Package Parameter changes to the correct value when executing the control package.
Please leave a reply if you have any comment or question
(Thanks Louw for making your Blog Site available for this article)
Big Data and Analytics 