SSIS FTP Task Variable Remote Path

  • SparTodd

    SSChasing Mays

    Points: 600

    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.

  • stevefromOZ

    SSC-Forever

    Points: 43646

    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

    SSChasing Mays

    Points: 600

    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

    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

    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

    SSChasing Mays

    Points: 600

    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. 😉

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

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