Archive for the ‘dtsx’ Tag

Failproofing Your SSIS Package

Here’s a script that you can add to your SSIS toolbelt. With this script, you can prevent an SSIS package from failing when it tries to import data from a flat file that isn’t there.

To start, we’ve got a simple package with a single Data Flow Task. If this package runs while the referenced flat file is missing, the package will fail.


To take care of this potential problem, we’ll add a Script Task that will check to see if the import file is there before proceeding with the Data Flow Task. Note the name of the Flat File connection (“Input Text File”) that points to our import file.


Here’s what’s inside the Script Task. We create a variable called filename, which we then assign the path to our import file by referencing the Flat File Connection in our package. The script then checks to see if the file is there.  If it is, the Script Task ends with a result of “Success” then continues on to the Data Flow Task, otherwise it will end with a result of “Failure”.


Here’s the code for your ctrl+c pleasure:

    Public Sub Main()
        ' Get file name from the "Flat File" Connection
        Dim filename As String = Dts.Connections("Input Text File").ConnectionString
        ' Check if the named file exists
        If System.IO.File.Exists(filename) Then
            Dts.TaskResult = Dts.Results.Success
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub

Now we can be sure that the package will not fail should our import file be missing for some reason. The error prevention of this package can be further enhanced by utilizing the “Failure” result of the Script Task and maybe adding a Send Email Task to send a notification that the import file is missing.

Hope this has been useful. Anybody have a different approach to solving this problem?