none
DateTime Stamp files deleteion from a folder using SSIS

    Question

  • Hi,
    There are files which are created automatically with DateTimeStamp Ex: <FileName>200907012025 is created on 7th July 2009 at 08:25 p.m. likewise there are daily two files generated. Now I want to delete these files based on the yyyy-mm-dd base, meaning the files which are more than 10 days older, they can be deleted.

    I am trying to use the variables in the Expression of Destination file connection manager like

    @[FileName] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()-10), 2) + ".txt"

    There is error :
    Expression cannot be evaluated.
    The data type "DT_DBTIMESTAMP" cannot be used with binary operator "-". The type of one or both of the operands is not supported 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 "GETDATE() - 10" failed with error code 0xC0047081.
    Evaluating function "DATEPART" failed with error code 0xC0047084.
    The data type "DT_DBTIMESTAMP" cannot be used with binary operator "-". The type of one or both of the operands is not supported 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 "GETDATE() - 10" failed with error code 0xC0047081.
    Evaluating function "DATEPART" failed with error code 0xC0047084.
    Evaluating function "RIGHT" failed with error code 0xC00470C5.

    Thanks,
    Monday, July 27, 2009 7:08 AM

Answers

  • use this



     @[FileName] +"_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd",(DATEADD( "dd", -10, getdate() )) ), 2) + ".txt"

    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
    Monday, July 27, 2009 7:55 AM

All replies

  • Hi meem,

    Since you want to delete the files older than 10 days, you can use a Execute SQL task and use "select getdate()-10" and capture the result into a datetime varibale (date)
    Now in your expression you can use
    @[FileName] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", @[User::date]), 2) + ".txt"

    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, July 27, 2009 7:49 AM
  • use this



     @[FileName] +"_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd",(DATEADD( "dd", -10, getdate() )) ), 2) + ".txt"

    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
    Monday, July 27, 2009 7:55 AM
  • hi Meemkay,
    This solution will work only for file created 10 days before todays date?
    When the package runs for the 1st time how do u delete the older files?
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Monday, July 27, 2009 10:31 AM
  • Hi Nitesh,
    I have created a variable 'TestDate' of DateTime and storing 'Select GetDate()-10' this value in that variable in Execute SQL Task. Is it necessary to map the parameters as 'Out Direction' of this 'TestDate' variable?  If so what shall I mention the parameter name?

    I am confused how to store this value?

    What do u mean by 'capture the value' ?

    Rahul and Sudeep,
    Thanks for kind reply and let me check Nitesh suggestion and I will check for you both suggestions and will reply you,

    Thanks,


    Tuesday, July 28, 2009 4:35 AM
  • Hi meemkay,

    Take Execute SQL Task and write the select statement (Select Getdate()-10) as the SQL Statement.
    Select the Result Set as "Single Row" in the General tab of Execute SQL Task editor.
    Then go to Result Set tab, set the Result Name as 0 and variable name as the datetime varible (TestDate) you have created. No need to do any parameter mapping.
    Now, you can use this TestDate variable in your expression.
    Please let us know if you face any issue
     
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, July 28, 2009 5:12 AM
  • Hi Nitesh,
    It is not working desirable, it is not deleting the files.
    I am using File System Task, The 'destination' folder includes the files like \\destination'\Test200907101259.txt etc and want to delete these which more than or equal to 10 days old files.
    In the File System Task the Destination Connection and Sorce Connection is configured as this 'destination'

    Is it necessary to change the file dynamically?

    Thanks,
    Tuesday, July 28, 2009 8:10 AM
  • Hi,
    Yes the file name needs to be changed dynamically as the files to be deleted today will be different from the files to be deleted tomorrow.
    Also as I said earlier this method will only delete the file that is 10 days old not the files older than 10 days.

    If you wish to delete files older than 10 days you need to store the value of the filenames in a dataset.
    Compare the dates in a script task to check which files needs to be deleted.

    Then in the foreach loop you need to delete these files.

    Infact if u are using script task to validate the file names you could delete the files in the script task itslef using a for loop. No need for the foreach loop task.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Tuesday, July 28, 2009 8:27 AM
  • Hi Sudeep,
    Thanks, Please let me know whether shall i go for the File System Task or Script Task ? As I have already explained you that, I am using File System Task and in the Source and Destination Connection points to the same file name in 'destination'. In the Destination Connection Manager > Properties > Expression > Connection String> I am including below,
    SubString(@[Dest_File] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", @[User::TestDate]), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt", 5,8)
    so as to get only date part
    Does this work?

    Thanks
    Tuesday, July 28, 2009 9:45 AM
  • hi,
    Thats exactly what I am saying.
    If the number of files is one or u can save the file names in an object variable you can go for File System task.

    But from your post I gather that u can have more than one files to delete the files with date older than 10 days.

    To do this using file system task u will have to validate each file in the folder before deleting them.
    This varification will have to be done in a script task.

    Hence I suggest that u use script task to do the varification and deletion of files in the script task itself.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Tuesday, July 28, 2009 10:36 AM
  • Hi Sudeep !

    I have tried it, but it is deleting the file which is generated currently. I will explain you the scenario in brief,

    I have 4 variables namely, i) Dest_Dir of string type with the value ':\Destination' ii) Dest_File of string type with value 'Test'  iii) Src_File of string type with value 'Test'  and iv) Test_Date of DateTime (value i have set as 0, but accepting by default as 12/30/1899)
    The file name is changed using the 'File System Task' which copies the file and place it by renaming the file name. Now I want to delete these files which are older than 10 days.
    Note: There is no 'Package Configuration', the file name is changing using File System Task and to delete the files I am using below expression,
    SUBSTRING( @[Dest_File] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", @[User::Test_Date]), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".bak", 1, 13 )

    Thanks,
    Wednesday, July 29, 2009 9:48 AM
  • Hi Nitesh,
    It is not working desirable, it is not deleting the files.
    I am using File System Task, The 'destination' folder includes the files like \\destination'\Test200907101259.txt etc and want to delete these which more than or equal to 10 days old files.
    In the File System Task the Destination Connection and Sorce Connection is configured as this 'destination'

    Is it necessary to change the file dynamically?

    Thanks,

     I know, it is not deleting the files created more than 10 days back.
    Is it deleting the files created 10 days back?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, July 29, 2009 10:06 AM
  • Hi Nitesh,
    Thanks for reply.
    Do u Guide me to code this using Script Task?

    Thanks,
    Wednesday, July 29, 2009 10:35 AM
  • Hi,
    no need to go to a file system task.

    Check the following link:
    FIle IO

    Check the section which talks about List Files.

    In the code FOR loop check the file name. Use sub string functions to validate the file whether it needs to be deleted or not.

    If the date you check is >=10 days delete the file in the loop itself.

    File.Delete(path) will be useful.

    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Wednesday, July 29, 2009 11:28 AM
  • Hi,
    An error while opening the above link........

    Thanks,
    Wednesday, July 29, 2009 11:35 AM
  • http://support.microsoft.com/kb/304427
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Wednesday, July 29, 2009 11:37 AM
  • Hi,
    Thanks, this link is helpful me to list the files in the directory. I have to read the first 13 characters of the filename[Test_200907101259.txt]. I have never used VB.Net Script. It would be helpful if u provide related links/articles. This kind of coding is new to me.

    Thanks,
    Wednesday, July 29, 2009 11:48 AM
  • Hi,

    Dim

     

    fn As String = "Test_200907101259.txt"

    fn = fn.Substring(5, 12)

    fn = fn.Substring(0, 4) +

    "/" + fn.Substring(4, 2) + "/" + fn.Substring(6, 2) 

     

    If CDate(fn) <= Now.AddDays(-10) Then

     

    'DELETE The file HERE

     

    End If

    Try this let me know if any issues.


    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Proposed as answer by Sudeep Raj Wednesday, July 29, 2009 12:09 PM
    Wednesday, July 29, 2009 12:08 PM
  • Check if you can give this a try:

    Take a for-each loop and iterate through the files. Define a integer varibale (num),with value
    as 10. Take a Script task that has num as ReadWrite varible with the following code:
                       Dts.Variables("num").Value =
                       Dts.Variables("num").Value + 1
    Then take Execute SQL Task with SQL Statement as : SELECT CONVERT(VARCHAR(20), GETDATE()-?, 112)
    Do the parameter mapping with variable num and capture the result set in a string variable (date)
    Then take File System Task:
                      IsSourcePathVariable   True
                      SourceVariableName    filename
    filename is the path for the files to be deleted for which you have a expression using date variable

    I have 5 files: File_20090719.txt,File_20090718.txt,File_20090717.txt,File_20090716.txt and File_20090715.txt
    Variables:
    VariabeName   DataType    Value/Expression
    --------------------------------------------------------
    num                int               10
    filename          string           @[User::Name] + REPLACE(SUBSTRING((DT_WSTR,22) @[User::date],1,9), "/","") + ".txt"
    date                string

    Also, please share the format of the file name



    Nitesh Rai- Please mark the post as answered if it answers your question
    • Edited by Nitesh Rai Wednesday, July 29, 2009 1:17 PM
    Wednesday, July 29, 2009 1:11 PM