SSIS CSV output with dynamic date ranges

Question

Hi everyone, I just started working with SSIS packages. For a project, I need to generate a csv file output and name it dynamically. The idea is to create a file every month on 20th with data which goes a month. Format should look like: ABC_20_JUNE_2021_to_20_JULY_2021.

I have worked out the part to generate current date, for example, 20 JULY_2021. But can't think of how I can generate 30 days back date dynamically every time. Any comment is appreciated.

Answer 1

Hi @delta smith ,

Not sure if you want is like below.

If this meet your requirement, you may use the expression to set the folder name with connection string.

"c:\\sql\\ABC_" + "20_"+  
(DATEPART("mm" , GETDATE()) == 2 ? "JAN" :  
DATEPART("mm" , GETDATE()) == 3 ? "FEB"  :   
DATEPART("mm" , GETDATE()) == 4 ? "MAR"  :  
DATEPART("mm" , GETDATE()) == 5 ? "APR"   :  
DATEPART("mm" , GETDATE()) == 6 ? "MAY" :   
DATEPART("mm" , GETDATE()) == 7 ? "JUN"  :  
DATEPART("mm" , GETDATE()) == 8 ? "JUL"  :  
DATEPART("mm" , GETDATE()) == 9 ? "AUG"  :  
DATEPART("mm" , GETDATE()) == 10 ? "SEP"  :   
DATEPART("mm" , GETDATE()) == 11? "OCT"  :   
DATEPART("mm" , GETDATE()) == 12 ? "NOV"  :   
DATEPART("mm" , GETDATE()) == 1 ? "DEC" :"")+  
RIGHT("_" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ), 5)+ "_to_20_"+  
(DATEPART("mm" , GETDATE()) == 1 ? "JAN" :  
DATEPART("mm" , GETDATE()) ==  2 ? "FEB"  :   
DATEPART("mm" , GETDATE()) == 3 ? "MAR"  :  
DATEPART("mm" , GETDATE()) == 4 ? "APR"   :  
DATEPART("mm" , GETDATE()) == 5 ? "MAY" :   
DATEPART("mm" , GETDATE()) == 6 ? "JUN"  :  
DATEPART("mm" , GETDATE()) == 7 ? "JUL"  :  
DATEPART("mm" , GETDATE()) == 8 ? "AUG"  :  
DATEPART("mm" , GETDATE()) == 9 ? "SEP"  :   
DATEPART("mm" , GETDATE()) == 10 ? "OCT"  :   
DATEPART("mm" , GETDATE()) == 11 ? "NOV"  :   
DATEPART("mm" , GETDATE()) == 12 ? "DEC" :"") +  
RIGHT("_" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ), 5) +".csv"  

Details for your reference:

https://www.mssqltips.com/sqlservertip/5869/ssis-expression-examples-for-dates-string-concatenation-dynamic-file-names-and-more/

Regards，

Zoe

---

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.
Hot issues October

Answer 2

Hi Zoe, Thanks heaps for responding. I may not have explained my problem correctly. When the file is generated on 20th of the a month it goes back a month (30 calendar days). Therefore, sometimes 19th of the previous month, other times 20th of the previous month. How is that I can dynamically adjust the expression to go back 30 days and append that date ? !

Answer 3

<current date> - 30

or maybe

adddate('dd', 30, <current date>)

Calculate the "start" prior to your formatting and then format both as desired

Answer 4

Here is snippet what I have written. The month part does not seem to work.

"ABC_"+ RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -30, GETDATE())),2)+"*"

• (MONTH(GetDate()) == 1 ? "JANUARY" : MONTH(GetDate()) == 2 ? "FEBRUARY" : MONTH(GetDate()) == 3 ? "MARCH" :
  MONTH(GetDate()) == 4 ? "APRIL" : MONTH(GetDate()) == 5 ? "MAY" : MONTH(GetDate()) == 6 ? "JUNE" :
  MONTH(GetDate()) == 7 ? "JULY" : MONTH(GetDate()) == 8 ? "AUGUST" : MONTH(GetDate()) == 9 ? "SEPTEMBER" :
  MONTH(GetDate()) == 10 ? "OCTOBER" : MONTH(GetDate()) == 11 ? "NOVEMBER" : MONTH(GetDate()) == 12 ? "DECEMBER" :
  "ERR") +"*"+SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 )