Post reply

how do i select previous date using ssis expression?

  • 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

  • July 4, 2013 at 11:57 pm

    #1629933

    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

  • July 5, 2013 at 12:08 am

    #1629938

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

    thanks

  • July 5, 2013 at 12:17 am

    #1629941

    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

  • July 5, 2013 at 1:11 am

    #1629953

    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"

  • July 5, 2013 at 1:18 am

    #1629959

    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

  • July 5, 2013 at 1:33 am

    #1629966

    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)

  • July 5, 2013 at 1:35 am

    #1629967

    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

  • July 5, 2013 at 1:48 am

    #1629973

    thanks i made it

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

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