question

deltasmith-4130 avatar image
0 Votes"
deltasmith-4130 asked RyanAbbey-0701 commented

SSIS CSV output with dynamic date ranges

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.

sql-server-generalsql-server-integration-services
5 |1600 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.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @deltasmith-4130,

Not sure if you want is like below.

117921-screenshot-2021-07-26-163230.jpg

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




5 |1600 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.

deltasmith-4130 avatar image
0 Votes"
deltasmith-4130 answered

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 ? !

5 |1600 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.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered

<current date> - 30

or maybe

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

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

5 |1600 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.

deltasmith-4130 avatar image
0 Votes"
deltasmith-4130 answered RyanAbbey-0701 commented

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 )

· 1
5 |1600 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.

Is that for your "From" date? You'd have to do the "-30" for every one of them

what might be slightly easier is
TOKEN("Jan|Feb|Mar", "|", month(dateadd('day', -30, getdate()))

you'll have to expand the months and adjust where necessary

1 Vote 1 ·