Expressions for Variables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Expressions for Variables

Expressions for Variables

Rate Topic

Display Mode

Topic Options

Author

Message

Regina Beauregard

Posted Monday, October 19, 2009 11:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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

stevefromOZ

Posted Monday, October 19, 2009 1:41 PM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

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 Smile

Steve.

Post #805256

Regina Beauregard

Posted Monday, October 19, 2009 1:53 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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

stevefromOZ

Posted Monday, October 19, 2009 2:09 PM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

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

Regina Beauregard

Posted Monday, October 19, 2009 2:17 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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

Regina Beauregard

Posted Monday, October 19, 2009 2:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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

stevefromOZ

Posted Monday, October 19, 2009 2:59 PM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

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

Regina Beauregard

Posted Monday, October 19, 2009 3:05 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:01 PM
Points: 34, Visits: 166

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 »

Permissions