SSIS FTP Task Variable Remote Path

Question

SSIS FTP Task Variable Remote Path

SparTodd

Right there with Babe

Points: 717
More actions
May 20, 2010 at 5:59 am

#147078

I'm trying to build SSIS package that will be scheduled to run daily Monday through Friday. The first part of the package requires an FTP task to download files from a server with a variable path. The variable path is dependent on the day of week. For example:
\reports\pdf\mon\
\reports\pdf\tue\
I know that I can use (DT_WSTR, 1) DATEPART("dw", GETDATE() ) to get the numeric day of week and convert it to text. I can even get it as far as REPLACE( (DT_WSTR, 1) DATEPART("dw", GETDATE() ), "5", "thu" ) and then I'm stuck.
My question is, how can I conditionally replace "2" with "mon", "3" with "tue" and so on?
I appreciate any suggestions.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

I could have sworn there was a case or switch statement, but you could always do the poor mans way - string together a set of IF statements.... format for IF in SSIS expressions is

boolean_expression?expression1:expression2

so something like.....

DATEPART("dw", GETDATE() ) == 5 ? 'thu' : DATEPART("dw", GETDATE() ) == 4 ? 'wed' : .......

Steve.

SparTodd Right there with Babe Points: 717 More actions · Answer 2

Thanks for the reply, stevefromOZ.

The "poor man's way", lol. That is probably the direction I'm headed in, but struggling through all the "if's" when there's no "else" operator.

You can tell I'm new to using the SSIS expressions.

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

The else is the right hand side of the colon

booleanexpression ? trueexpression: falseexpression

so what I might write in vb or similar

If today = "friday" Then

"happy days!"

Else

"week draggin on"

End If

might be (in my SSIS package)

today == "friday" ? "happy days!" : "week draggin on"

Steve.

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

or is the struggle more that there is no 'else' written there, you have to search (visually) for the colon? It's a bit of a PITA, and would be really nice if switch was supported 🙁

Steve.

SparTodd Right there with Babe Points: 717 More actions · Answer 5

Thanks, Steve, your second example helps some more. I agree, the switch would be helpful.

Since it is Friday, I'm probably not going to think about this project again until Monday. 😉