In SSIS when you are running a package with a parameter I find myself wanting to set the Project Parameters at run time. You can do this by creating an Enviroment Variable and then running the below script to set it’s value. The Project Parameter is named LoadDate and this script is setting it to yesterday’s date.
DECLARE @var DATETIME =CONVERT(DATE,DATEADD( DAY, -1 , Getutcdate()))
EXEC [SSISDB].[catalog].[set_environment_variable_value] @variable_name=N’LoadDate’,
@environment_name=N’EDWSettings’, @folder_name=N’PaySpanEDW_Sources’, @value=@var
Here are three scripts that create and Date and Time Dimension and can add the fiscal columns too. First run the Dim Date script first to create the DimDate table. Make sure you change the start date and end date on the script to your preference. Then run the add Fiscal Dates scripts to add the fiscal columns. Make sure you alter the Fiscal script to set the date offset amount. The comments in the script will help you with this.
This zip file contains three SQL scripts.
Create Dim Date
Create Dim Time
Add Fiscal Dates
These will create a Date Dimension table and allow you to run the add fiscal script to add the fiscal columns if you desire. The Create Dim Time will create a time dimension with every second of the day for those that need actual time analysis of your data.
Make sure you set the start date and end date in the create dim date script. Set the dateoffset in the fiscal script.
Download the script here: