Post reply

Add Current Date to File Name

  • May 19, 2009 at 8:05 am

    #131196

    Hello,

    I need to come up with a way to add the current date to a daily .csv file name and then upload just that single file to an FTP site.

    I already have an SSIS package that builds the file. Now I need to rename it with the date (20090519_data.csv) and upload it.

    Any suggestions?

    Thanks

  • May 19, 2009 at 8:22 am

    #997016

    Prefix the destination variable with

    (DT_WSTR,4)YEAR(@[User::DateTimeVar])

    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)

    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)

    For more sample date-time formats, click here

    --Ramesh

  • May 19, 2009 at 9:15 am

    #997088

    Thanks for the reply Ramesh.

    I'm having a hard time getting this to work. Here's what I'm attempting to do.

    I've added the "User::DateTimeVar" variable to the package. Then I created a "File System Task" and tried to add your expression to the destination variable. Here's the error that I get when I try to evaluate the expression.

    TITLE: Expression Builder

    ------------------------------

    Expression cannot be evaluated.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    The function "YEAR" does not support the data type "DT_I4" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

    Evaluating function "YEAR" failed with error code 0xC0047089.

    The function "YEAR" does not support the data type "DT_I4" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

    (Microsoft.DataTransformationServices.Controls)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • May 19, 2009 at 9:21 am

    #997095

    Can you post the expression you are trying to evaluate?

    --Ramesh

  • May 19, 2009 at 9:37 am

    #997116

    Here's a screen shot...

  • May 19, 2009 at 9:43 am

    #997122

    It was my bad!!! This is what happens when you post late in the night:w00t:

    Change the expression to

    (DT_WSTR,4)YEAR(GETDATE())

    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)

    + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + ".CSV"

    --Ramesh

  • May 19, 2009 at 10:13 am

    #997145

    The expression evaluates now. However...I get this error.

    TITLE: Package Validation Error

    ------------------------------

    Package Validation Error

    ------------------------------

    ADDITIONAL INFORMATION:

    Error at File System Task: Failed to lock variable "D:\20090519-acct.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    Any ideas?

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

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