Search code examples
sqlsql-serverssisdts

How to remove or delete a .txt file on SQL Server using DTS Package?


What i wanted to achieve is to use DTS Package to remove a .txt file on a scheduled basis in order for another package to pick it up which in this case im using SSIS as a next step, it's just that i dont know how to delete a text file using DTS Package.


Solution

  • The preferred way to delete a file in SSIS (no need to create any more dinosaur DTS / activex packages these days), is to use a script task. Here's the vb.net way:

    Make sure you reference System.IO:

    Imports System.IO
    

    Then just run the following (replacing the filename value with the file you want to delete):

    Public Sub Main()
        Try
            Dim filename As String = "C:\fileYouWantToDelete.txt"
            If File.Exists(filename) Then
                File.Delete(filename)
            End If
        Catch ex As Exception
            Dts.Events.FireError(0, "File Killer", ex.Message, String.Empty, 0)
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
    

    Here's a link to the DTS / vbscript way (although I highly recommend not to create dts packages, as the product is dead and someone will just have to convert your code in a few months/years): http://www.codekeep.net/snippets/187006e9-bce2-42cd-85fd-231eced17c80.aspx