SSIS - Get Year based on Month

Question

Good morning. I have a bit of a quandary. I have an SSIS package that dynamically imports files from the previous month. The files are coded with the name of the month in text and the year. I have the month working correctly. The year is not working correctly. It currently gets the year based on the system date. The problem is if it is January, it should return the previous year. I have two variables that hold the month and year. It would be great if somebody could shine some light on this dilemma.

SUBSTRING("December  January   Febuary   March     April     May       June      July      August    September October   November  ",((DATEPART("MONTH",GETDATE())-1)*10)+1,9)  
  
REPLACE(SUBSTRING((DT_STR,50,1252)GETDATE(),1,4),"-","_")  

Answer 1

Hi @Cynthia McMahon ，

If the date format is like '2022-Dec-09', you may use expression to get the previous year if the month is Jan.

SUBSTRING(yourdatecolumn,6,3)== "Jan" ?(YEAR( (DT_DBTIMESTAMP)(yourdatecolumn))-1):YEAR( (DT_DBTIMESTAMP)(yourdatecolumn))  

You may modify the expression with your date setting.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Cynthia McMahon ,

(1) You would need to add '01' to your input to compose a real date in the following format: yyyy-MM-dd:

<your_input_date> =  (DT_DATE)('yyyy' + "-" + "MM" + "01")  

(2) After that use DATEADD() function to go back one month:

DATEADD("Month", -1,<your_input_date>)  

(3) Use DATEPART() function to get YEAR and MONTH for your needs.