Pass first and last day of the previous month in SSIS Execute SQL Task

Tadishetty, Sandeep 101 Reputation points
2021-02-17T23:50:54.947+00:00

Hi,

I need to execute a stored procedure using execute sql task component in ssis and the SP parameters should be first day of previous month and last date of previous month. Please let me know the process.

Currently running manually and below is the Oracle SP given by developer to me to run using Execute SQL task for this month:

Begin

SP_TEST('1-Jan-2021','31-Jan-2021');

end;

Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,831 Reputation points
    2021-02-18T01:07:20.577+00:00

    It is possible to calculate first and last day of the previous month by using built-in EOMONTH() T-SQL function.

    You can do the calculations inside the stored procedure via T-SQL. Just pass a single DATE parameter: @givenDate

    SQL

    DECLARE @givenDate DATE = '2021-04-27';
    
    SELECT EOMONTH(DATEADD(month, -1 , @givenDate)) AS 'Last Day of the Previous Month';
    SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(month, -2 , @givenDate))) AS 'First Day of the Previous Month';
    

    UPDATE
    SSIS Expressions are not powerful enough for this task. It is much easier to use SSIS Script Task for it. You can use c# below as a starting point. Just assign its outcome to two SSIS variables, and pass them as parameters to Oracle stored procedure.

    c#

    void Main()
    {
     DateTime today = DateTime.Today;
     DateTime monthStart = new DateTime(today.Year, today.Month, 1);
     DateTime FirstDayofthePreviousMonth = monthStart.AddMonths(-1);
     DateTime LastDayofthePreviousMonth = monthStart.AddDays(-1);
    
     Console.WriteLine("FirstDayofthePreviousMonth='{0}', LastDayofthePreviousMonth='{1}'"
     , FirstDayofthePreviousMonth.ToString("dd-MMM-yyyy")
     , LastDayofthePreviousMonth.ToString("dd-MMM-yyyy"));
    }
    

    Output
    FirstDayofthePreviousMonth='01-Jan-2021', LastDayofthePreviousMonth=31-Jan-2021


2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-02-19T02:13:23.54+00:00

    Hi @Tadishetty, Sandeep

    The method of Yitzhak Khabinsky-0887 is a good idea. In addition, other methods include:

     select dateadd(mm,datediff(mm,0,dateadd(month, -1 ,getdate())),0) as 'First Day of the Previous Month'  
     select dateadd(ms,-2,dateadd(mm, datediff(m,0,dateadd(month, -1 ,getdate()))+1, 0)) as 'Last Day of the Previous Month'  
    

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Tadishetty, Sandeep 101 Reputation points
    2021-03-08T17:02:26.83+00:00

    Thank you Echo and YitzhakKhabinsky.

    I tried the following method provided by my teammate and it worked.

    Step - 1: Created two variables StartDate & EndDate with string data type.

    Step -2: Used Executes SQL task with following statements and in the result set selected both the variables.

    SELECT UPPER(FORMAT(DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)), 'dd-MMM-yyyy')) StartDate, UPPER(FORMAT(DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)), 'dd-MMM-yyyy')) EndDate

    Note: I connected to master database but we can connect to any database for SQL connection

    STep-3: Used another Execute SQL task and called Oracle SP by selecting the StartDate and EndDate variables in the parameter mapping tab and it worked.

    Thanks,
    Sandeep