Search code examples
ssis-2012

Variable for yesterday's date in SSIS

I have declared a variable called FileName1 in my package with the value - 

"Exchange_"+(DT_WSTR,4)  DATEPART( "YEAR", GETDATE() )+"-"+
 RIGHT( "0"+(DT_WSTR,2) DATEPART( "month" , GETDATE()) , 2) +"-"+
 RIGHT( "0"+(DT_WSTR,2) (DATEPART( "day", GETDATE()  )-1) , 2 ) +
".txt".

What it does is it takes yesterday's date. But if I click on the Evaluate Expression on 1st day of the month (say 1st December), instead of showing "Exchange_2015-11-30.txt", its showing "Exchange_2015-12_00.txt". How can I fix this?

Solution

  • You need to do it like the following:

    "Exchange_"+ (DT_WSTR,4) DATEPART("yyyy", DATEADD( "d", -1, getdate() ))+"-"+ (DT_WSTR,2) DATEPART("mm", DATEADD( "d", -1, getdate() )) +"-"+ (DT_WSTR,2)DATEPART("dd", DATEADD( "d", -1, getdate() ))  + ".txt"