Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Delete text file in the folder

Answered Delete text file in the folder

  • Friday, January 04, 2013 11:44 PM
     
     
    Sign In to Vote
    0

    I know you can use Execute SQL Task to execute SQL command within the SQL Server, but how do you delete text file located on a hard drive (ie, c:\abc.txt) using SSIS task? 

    BI Analyst


    • Edited by BIAnalyst Friday, January 04, 2013 11:44 PM
    •  
     

All Replies

  • Saturday, January 05, 2013 1:24 AM
     
     Proposed Answer
    Sign In to Vote
    1

    Hi BIAnalyst,

    It should be pretty straightforward by using File System Task. Double click the File System Task, go to General -> Operation and select "Delete File". Configure your SourceConnection and you are good to go.

    HTH

    Regards,
    Santosh

    http://microsoftbizintel.wordpress.com/

    • Proposed As Answer by Aalam Rangi Saturday, January 05, 2013 4:20 AM
    •  
     
  • Saturday, January 05, 2013 3:43 AM
     
     
    Sign In to Vote
    0

    Hi Santosh,

    Thank you for your help.  It works well.  Do you or anyone know how to delete multiple files in one shot?

    Also, is there a way to delete the logging text file because I am getting a "the file is used by another user"?


    BI Analyst

     
  • Saturday, January 05, 2013 4:12 AM
     
     Proposed Answer
    Sign In to Vote
    1

    Hi BI Analyst,

    To delete all the files from a folder, follow below steps -

    Double click the File System Task, go to General -> Operation and select "Delete directory content". Configure your SourceConnection and this time to a folder, so please select Usage type as Existing Folder. You are done. You can also customize deleting certain files by changing the connection string dynamically based on your business logic.

    You are not able to delete your log files because looks like it's used by other process. The other process need to relinquish the log file and then you will be able to delete it.

    HTH

    Regards,
    Santosh

    http://microsoftbizintel.wordpress.com/

    • Proposed As Answer by Aalam Rangi Saturday, January 05, 2013 4:19 AM
    •  
     
  • Saturday, January 05, 2013 4:34 AM
     
     
    Sign In to Vote
    0

    Hi Santosh,

    Thanks again for your help.  Yes, I will need to delete certain files and not every file in the folder.  In such case, how can I set up the connection string dynamically?  Do I also need to use a Foreach Loop container?  Basically, I want to delete all file that start with "xxx" in the folder.  For example, "xxx1.txt", "xxx2.txt" and so on.

    BI Analyst

     
  • Saturday, January 05, 2013 4:43 AM
     
     
    Sign In to Vote
    1

    Hi BI Analyst,

    You are in right direction. In For Each container there is a property "FileSpec", you can explore it and use it for your logic. Inside your ForEach loop you can put logic for deleting the file.

    May be below link, could be of your some help -

    http://beyondrelational.com/modules/2/blogs/106/posts/11123/ssisdynamically-set-file-mask-filespec.aspx

    Regards,
    Santosh

    http://microsoftbizintel.wordpress.com/

     
  • Sunday, January 06, 2013 3:23 AM
     
     
    Sign In to Vote
    0

    Hi Santosh,

    After spent some times trying, I still cannot get it.  I cannot see the "FileSpec" property for the Foreach Loop Container.  Here is what I did and perhaps you or someone can guide me to the right direction.

    I created

    a variable, filename2, with String type and package scope.

    a File Connection, destination2, with the following setting:

    Usage type: Existing folder

    Folder: C:\SSIS\Temp

    Expression property | ConnectionString: @[User::filename2]

    Enumerator: Foreach File Enumerator

    Folder: C:\SSIS\Temp

    Files: *.txt

    a File System Task with the folllowing setting:

    Operation: Delete file

    SourceConnection: destination2

    The File System Task is in the Foreach Loop Container.

    On the Foreach Loop Container, there is a red "x" and when I hovered the mouse cursor over it, it says "Type of Connection 'destination2' is not a file."

    How can I correctly setting up the task?

    (I have one more Foreach Loop Contain that has a File System Task that copy files from one folder to another.  That goes well.  Also, I do not want to delete the "all content in the directory" because there are some files in the folder that I want to keep.)

    BI Analyst


    • Edited by BIAnalyst Sunday, January 06, 2013 3:24 AM
    •  
     
  • Sunday, January 06, 2013 11:12 PM
     
     Answered
    Sign In to Vote
    0

    I used Flat File Connection instead of the File Connection and it worked!  The reason is that deleting *.txt files are a "file level" action and not a "folder level".  If I were deleting everything in the folder, then I would use the File Connection to define a folder.  

    I forget to mention that in the Foreach container, I have to assign the variable in Variable Mapping.

    BI Analyst

    • Marked As Answer by BIAnalyst Sunday, January 06, 2013 11:12 PM
    •