SSIS Variables as expressions to generate Start and end dates of a quarter with input year and month passed as variable

Question

Can someone help me create SSIS expressions for variables to generate QuarterStart(DateTime) and QuarterEnd(DateTime) as a variable with year(int32) and quarter(int32) passed on as user variables.

The equivalent T-SQL script would be

    @FISCAL_YEAR INT = 2023,
    @QUATER INT = 1
SELECT 
    DATEFROMPARTS(@fiscal_year,(@Quater * 3)-2,1) AS QuaterStart,
    EOMONTH(DATEFROMPARTS(@fiscal_year,@Quater * 3,1)) AS QuaterEnd

Answer 1

My easy way to store the values by executing a sql task for populating a SSIS variable.

1. Create two SSIS variables with datetime as data type QUATER_START_DATE and QUATER_END_DATE

2. Create an Execute SQL Task

3. Add the script already in the question in to this task.

   declare   @FISCAL_YEAR INT = 2023,
   @QUATER INT = 1
   SELECT 
   DATEFROMPARTS(@fiscal_year,(@Quater * 3)-2,1) AS QuaterStart,
   EOMONTH(DATEFROMPARTS(@fiscal_year,@Quater * 3,1)) AS QuaterEnd
   

4. Change the Result set property to Single

5. Click Add in the Result Set section of Execute SQL Task

6. Set the Result Name as column name of the sql command and map it to the respective SSIS variables.

Once this task is executed it will populated with the values returned by the sql statement.