how do i select previous date using ssis expression?

Question

how do i select previous date using ssis expression?

ashuthinks

SSCommitted

Points: 1698
More actions
July 4, 2013 at 11:32 pm

#295304

hi i have ssis package and following expression which gives me todays date and time for file name
@[User::FilePath]+ "Bloomberg_"+REPLACE((DT_STR, 20, 1252)
(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"
\\public\\Bloomberg_Upload\\Bloomberg_2013-07-05 005738.xls
I need to get one date previous like following only for weekdays:
\\public\\Bloomberg_Upload\\Bloomberg_2013-07-04 005738.xls
How can I do this ?
For Monday -
If I execute my package on Monday date should be of Friday.
please guide me

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

This is going to be a very long expression.

First check on which day the Starttime falls using the DAY function. If it is a monday, decrement it with 3 using DATEADD. If is is a Sunday, decrement it with 2. For all other days, decrement with 1.

This is probably easier in a script component.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ashuthinks SSCommitted Points: 1698 More actions · Answer 2

could you please help me in long expression , m total new bie

thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

ashuthinks (7/5/2013)
could you please help me in long expression , m total new bie
thanks

Why don't you give it a shot first and then post back if you run into problems?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ashuthinks SSCommitted Points: 1698 More actions · Answer 4

i'm trying like this -

(DT_I4)DATEPART("weekday",@[System::StartTime]) ==2 ?

Replace((DT_STR, 20, 1252)(DATEADD( "D", -3,@[System::StartTime])),":","-") + ".xls" :

Replace((DT_STR, 20, 1252)(DATEADD( "D", -1,@[System::StartTime])),":","-") + ".xls"

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

That seems to be working.

Now just nest another conditional check to check for Sundays, and you're all set.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ashuthinks SSCommitted Points: 1698 More actions · Answer 6

no need of sunday to me problem is unable to take filename now ;(

"FILENAME"+

(DT_I4)DATEPART("weekday", GETDATE() ) ==2 || (DT_I4)DATEPART("weekday",@[System::StartTime]) ==7 ?

Replace((DT_STR, 20, 1252)(DATEADD( "D", -3,@[System::StartTime])),":","-") + ".xls" :

Replace((DT_STR, 20, 1252)(DATEADD( "D", -1,@[System::StartTime])),":","-") + ".xls"

getting error -

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation "@[User::AnimalName] + (DT_I4)DATEPART("weekday",@[System::StartTime])" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

I answered in your other thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b210497-77a7-4241-9327-832a876dc1a4/how-do-i-select-one-date-prior-using-ssis-expression

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ashuthinks SSCommitted Points: 1698 More actions · Answer 8

ashuthinks

SSCommitted

Points: 1698

July 5, 2013 at 1:48 am

#1629973

thanks i made it