question

TadishettySandeep-8327 avatar image
0 Votes"
TadishettySandeep-8327 asked ·

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

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
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

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 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,


Actually, I do not have control of this particular Oracle SP which was written by Oracle developer and asked me to automate using ETL package. Is there a way that I can use the expressions to automate using SSIS.

Thanks,
Sandeep

0 Votes 0 ·

@TadishettySandeep-8327,

I updated my answer with the alternative solution via SSIS Script Task.
Please check its UPDATE section.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @TadishettySandeep-8327

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 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,


Actually, I do not have control of this particular Oracle SP which was written by Oracle developer and asked me to automate using ETL package. Is there a way that I can use the expressions to automate using SSIS.

Thanks,
Sandeep

0 Votes 0 ·
EchoLiu-msft avatar image EchoLiu-msft TadishettySandeep-8327 ·

No, SSIS can only choose which tables or TSQL statements to use. TSQL statement and SSIS are two separate processes. SSIS can use TSQL statements, but TSQL cannot call SSIS.

As far as I know, the feasible method is to manually run or modify the stored procedure.

Regards
Echo

0 Votes 0 ·
TadishettySandeep-8327 avatar image
0 Votes"
TadishettySandeep-8327 answered ·

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

· 4 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TadishettySandeep-8327,

Glad to hear that you are making a progress.
Though I already provided you how to do the calculations long time ago:

  • in T-SQL.

  • via SSIS Script Task.

0 Votes 0 ·

I apologies to post another link in this thread but can you please also help me in finding the solution for the following issue as well.

https://docs.microsoft.com/en-us/answers/questions/304678/mm-dd-yyyy-hhmmss-in-excel-destination-using-ssis.html


Thanks,
Sandeep

0 Votes 0 ·
EchoLiu-msft avatar image EchoLiu-msft TadishettySandeep-8327 ·

I am glad that your problem has been resolved.
Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

0 Votes 0 ·
Show more comments