Expressions for Variables

Question

Expressions for Variables

Regina Beauregard

SSChasing Mays

Points: 626
More actions
October 19, 2009 at 11:50 am

#205792

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.

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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.

Regina Beauregard SSChasing Mays Points: 626 More actions · Answer 2

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

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.

Regina Beauregard SSChasing Mays Points: 626 More actions · Answer 4

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.

Regina Beauregard SSChasing Mays Points: 626 More actions · Answer 5

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

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.

Regina Beauregard SSChasing Mays Points: 626 More actions · Answer 7

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.