Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Expressions for Variables Expand / Collapse
Author
Message
Posted Monday, October 19, 2009 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, Visits: 167
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.

Post #805212
Posted Monday, October 19, 2009 1:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456
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.
Post #805256
Posted Monday, October 19, 2009 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, Visits: 167
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.
Post #805259
Posted Monday, October 19, 2009 2:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456
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.
Post #805268
Posted Monday, October 19, 2009 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, Visits: 167
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.
Post #805269
Posted Monday, October 19, 2009 2:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, Visits: 167
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.
Post #805271
Posted Monday, October 19, 2009 2:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456
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.
Post #805288
Posted Monday, October 19, 2009 3:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:12 AM
Points: 34, Visits: 167
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.
Post #805289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse