Expressions for Variables

  • I need help for creating an expression as a variable in SSIS. I need to pass a variable for the current Period -1 to a data flow task. However, I need to prefice the variable with a Zero. for instance,

    Period 1 = 01

    Period 12 = 012

    I can get this to work for the current period but not for period -1. This is the expression I have tried.

    "0" + (DT_WSTR,30)DatePart("mm", GETDATE())

    Can someone give me a hand with this.

  • Based on your expression for current, previous would be very similar... Note that the below will 'roll back' from a Period 01 to Period 012.

    IIf(DatePart("mm", GETDATE()) == 1 , "012", "0" + (DT_WSTR,30)DatePart("mm", GETDATE()))

    You don't mention it but make sure you account for year 🙂

    Steve.

  • Thanks for your reply but isn't (IIf) a Microsoft Access function. I am trying to create a variable as an expression in an SSIS package. I tried what you gave me but I got an error stating IIF is unrecognized.

  • Sorry, looks like it's supported in 2008, perhaps not 2005. (here)

    You can use the shortcut version of If/Iff , like .....

    DatePart("mm", GETDATE()) == 1 ? "012" : "0" + (DT_WSTR,30)DatePart("mm", GETDATE())

    Steve.

  • Thank you again for your reply. It works and gives me 012 but I really need the expression to give me the current month -1 formatted with a preceeding 0. If I have to change it every month then there is no point to using a variable.

    DatePart("mm",GetDate()) -1 works fine and returns 9 but what I need it to return is 8. I need an expression that I don't have to change.

  • Thank you for the code and please disregard my previous reply. I get 010 when I use your example as a variable. What I need is 009 and that is where I'm having the problem. I need the current month -1 all the time. I have tried everything to get that to work.

  • so using...

    DatePart("mm", GETDATE()) == 1 ? "012" : "0" + (DT_WSTR,30)(DatePart("mm", GETDATE()) - 1)

    doesn't work?

    (added the minus one at the end).

    Steve.

  • Thanks again, it does work now. I must have had another syntax error. I appreciate all your help with this.

    Have a wonderful evening. Perhaps someday I can come to your assistance.

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

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