0

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

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.