none
Rename file using File System Task Editor

    Question

  • Could someone please instruct me on how to use the File System Task Editor to rename a file?  I place control on control flow tab, change the operation to rename, from there I am not sure what to do.
    Thursday, July 13, 2006 9:34 PM

Answers


  • valid Expression:

    "\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"


    Datepart returns a number.  You can't concatenate a number to a string.  Instead, you need to cast the number to a string.  I have done so for you in the example.
    Thursday, May 24, 2007 7:26 PM
  • Create two package variables called FileSource and FileDestination.

    Assign the path+existing filename  to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

    In the FileSystemTask properties -
    Set Operation to 'Rename File'
    Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
    Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

    When you execute this task - you will find that the source file is renamed as destination file.

    Thanks,
    Loonysan

     

    Thursday, July 13, 2006 11:30 PM
  • Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.
    Monday, November 20, 2006 5:43 PM
  • A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

    Why are you challenged, the expression itself looked good.

    Tuesday, October 31, 2006 4:01 PM

All replies

  • Create two package variables called FileSource and FileDestination.

    Assign the path+existing filename  to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

    In the FileSystemTask properties -
    Set Operation to 'Rename File'
    Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
    Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

    When you execute this task - you will find that the source file is renamed as destination file.

    Thanks,
    Loonysan

     

    Thursday, July 13, 2006 11:30 PM
  • Please post the exact syntax the destination variable.
    I am challenged by something like:
    "\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"
    Tuesday, October 31, 2006 3:53 PM
  • IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?
    Tuesday, October 31, 2006 3:57 PM
  • A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

    Why are you challenged, the expression itself looked good.

    Tuesday, October 31, 2006 4:01 PM
  • Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

    "C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

    and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

    I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

    What am I missing here please?
    Friday, November 03, 2006 10:52 PM
  • Thanks for your reply, Phil.
    Gets or sets a Boolean that indicates that the variable contains an expression.
    That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.
    Monday, November 20, 2006 4:39 PM
  • Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK.   So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

    Thanks again,
    IanO
    Monday, November 20, 2006 4:44 PM
  • Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.
    Monday, November 20, 2006 5:43 PM
  • I am sorry to ask but where do you create thise package variables?
    Thursday, May 24, 2007 3:50 PM
  •  Sugo wrote:
    I am sorry to ask but where do you create thise package variables?


    In the variables window.  View->Other Windows->Variables
    Thursday, May 24, 2007 4:02 PM
  • Thanks for the help,

     

    I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

     

    My package has a data export task that goes to a flat file export task.  Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile.  Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues.  I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me.  I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches. 

     

    Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

     

    Expression:

    "\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

     

    Error:

    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.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
    " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

     (Microsoft.DataTransformationServices.Controls)

    Error w/o the cast:

    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.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

    Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

     (Microsoft.DataTransformationServices.Controls)

     

     

    Thanks,
    Wayne

     

     

    Thursday, May 24, 2007 7:21 PM

  • valid Expression:

    "\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"


    Datepart returns a number.  You can't concatenate a number to a string.  Instead, you need to cast the number to a string.  I have done so for you in the example.
    Thursday, May 24, 2007 7:26 PM
  • Not sure if this is the correct way but it seems to work ok.

     

    "\\\\ServerName\\Share\\filename" + "_"  + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

    Thursday, May 24, 2007 7:35 PM
  •  Sugo wrote:

    Not sure if this is the correct way but it seems to work ok.

    "\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))



    Either way would work...
    Thursday, May 24, 2007 7:42 PM
  • Phil

     

    I am using "D:\\stp\\worldcheck\\archive\\world-check-week.csv" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".CSV" as an expression in a rename file system task and get this error....... cannot convert 'system.string' to 'system.int32'

     

    Any ideas 

    Thursday, May 31, 2007 11:20 AM
  • Try

    Code Snippet

    "D:\\stp\\worldcheck\\archive\\world-check-week.csv" + ((DT_WSTR,4)DATEPART("yyyy",GETDATE())) +".CSV"

     

    Phil's expression worked fine for me, but wrapping that in an extra set of paranthesis should ensure that the conversion happens before the concatenation.

    Saturday, June 02, 2007 1:07 AM
  • Hello,

     

    I correctly configure the package but i get this error:

    Code Snippet

    Failed to lock variable "xxx" 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.".

     

     

    Do you know how ti unlock the varaiable?

     

    Thanks

     

     

    Monday, July 02, 2007 9:47 AM
  • What is the scope of the variable? The error message indicates that the variable can't be found.
    Monday, July 02, 2007 2:18 PM
  • Double check the variable name is correct, and remember they are case sensitive.
    Friday, July 06, 2007 7:34 AM
  •  

    Hi,

     

    Please could you explain to me how I can add a date stamp to my out put flat file.

    You've mention that I can use an expression within the connection string of the flat file.

    Would that expression need to be within File Name path specified ?

     

    So for example the path for my file is eg;

     

    C:\Documents and Settings\emma hardie\Desktop\POLines.txt

     

    If I want to add a datetime stamp to this text file every time it generates what do I need to do ?

     

    Your help would be appreciated

     

    Em

    Wednesday, September 26, 2007 5:01 PM


  • Go to the properties of the flat file connection manager, 'Expressions' property; and add an expression to the 'Connection string' property. The expression should look like the expressions given by Phil or John in the posts above.
    Wednesday, September 26, 2007 5:32 PM
  • This worked for me:

    @[FileDir] + "\\" + @[FileDest] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + "_" + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".csv"
    Thursday, August 20, 2009 12:06 PM
  • This worked for me:

    @[FileDir] + "\\" + @[FileDest] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + "_" + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + "-" + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".csv"
    Thursday, August 20, 2009 12:06 PM
  • Thanks.

    Requirement Details:

    I have two folders Name : Excel_Source & Excel_Target. In Excel_Source, I have Excel file Name Country_Name. I have created a package to move this excel file Country_Name from Folder Excel_Source to Excel_Target folder and then want to change Excel File name from Country_Name to A, B or C. This value A, B or C is coming from database and it has been holding by Variable name V_File_ Name.

    Here Is the Solution:

    • Step1: Create a template excel file (Country_Name.xls) in C:\ Excel_Source\ folder
    • Step2: Create 3 variables (string type and package scoped) named :
    •  
      1. V_Target with value as : C:\ Excel_Target\ (folder where new file is to be created)
      2. V_Target_File with "Evaluate as Expression" property set as TRUE. then set the expression as:[ " C:\ Excel_Target \" + @V_File_ Name + ".xls" or (“.xlsx”) ]OR  [@ V_Target + @V_File_ Name + ".xls"]
      3. V_Source_File with value as : C:\ Excel_Source\ Country_Name.xls
    • Step3: Take one File System Task and open the File System Task editor.

    *   Select Operation as "Rename File"

    *   Set IsDestination path variable to TRUE and select V_Target_File from drop down box as variable.

    *   Keep IsSourcePath variable as TRUE and select V_Source_File from drop down box as variable.

     

    Or Follow this link: http://beyondrelational.com/blogs/niteshrai/archive/2010/04/05/creating-a-file-using-ssis-file-system-task.aspx


    Thanks Shiven:)

    • Proposed as answer by S Kumar Dubey Monday, June 20, 2011 9:41 AM
    • Unproposed as answer by S Kumar Dubey Monday, June 20, 2011 9:42 AM
    • Proposed as answer by S Kumar Dubey Monday, June 20, 2011 9:42 AM
    Monday, June 20, 2011 7:22 AM
  • Usually I used expression:

    "C:\\folder\\filename_"+SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+".txt"

    As a result I get:

    "C:\folder\filename_2013-01-13.txt"

    Friday, January 18, 2013 9:38 AM