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

I would address this with three variables all of type DateTime

What is last month?

The first thing I need to know is "what is now?". I can use GetDate() but I advocate for using the System variable StartTime as the value is consistent for the entire execution of your package whereas getdate is evaluated every time you inspect it. Assume your package runs for 2 minutes and starts at 11:59 on 2021-03-31. When the package starts, the previous month is Feb but when it finishes, it's March. Using @[System::StartTime] solves that problem.

Create a variable called PreviousMonth and use the following expression

DATEADD( "Month", -1, @[System::StartTime])

That will subtract a month from our current date resulting in 2021-01-17

What is the start of last month?

Now that we know what last month is, the first of the month is "easy" although it's going to look messy. This variable I call PreviousMonthFirst

(DT_DATE) ((DT_WSTR, 4) YEAR( @[User::PreviousMonth]) + "-" + (DT_WSTR, 2) MONTH( @[User::PreviousMonth]) + "-1")

Let's look at that in pieces. First piece extracts the year from the previous month and converts to a string.

(DT_WSTR, 4) YEAR( @[User::PreviousMonth])

Now that we have 2021 as string, let's add in a dash delimiter. String concatenation is the + operator

+ "-" +

Now we have 2021- so the next step is to repeat the year logic but for month

(DT_WSTR, 2) MONTH( @[User::PreviousMonth])

We now have 2021-1 built out and we can simply concatenate the dash one to the string

+ "-1"

Excellent, we have built the string of 2021-1-1 all we need to do is cast all of that string to a type of DT_DATE which is why all of the above is wrapped with a cast.

(DT_DATE) (....)

An alternative approach would be to use date math to substract away the days in the month to make it one. Use dateadd to subtract, -1*, days. How many days? However many days we currently have but add 1 back to get the first

(DT_DATE) DATEADD("DAY", -1 * DAY( @[User::PreviousMonth]) +1,  @[User::PreviousMonth])

I reached for the string manipulation first but this one works as well.

What is the end of last month?

While I could code in the rules for days of month, I'm going to let the libraries do the work. Add a month to the first of the month resulting in Feb 1. Then subtract a day and so that's the formula I use for PreviousMonthLast

DATEADD("Day", -1, DATEADD("Month", 1,  @[User::PreviousMonthFirst]))

An important thing to consider in all of this is whether your system expects date only or date and time. The value of PreviousMonthLast will be "2021-01-31" but if your procedure expects date and time, then it will coerce that to midnight of Jan 31 which will lose you 23 hours, 59 minutes, 59 seconds and however much precision your system has in milliseconds.

If time does matter, then swap out Day for Second in this section.

Final thoughts

If your procedure expects a text representation of date like '1-jan-2021' you're going to have to write your own logic as the expression language does not support a DateName like function. Or just use a script task and a few lines of .NET to derive first and last of the prior month.

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