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?


2 comments so far

  1. greg on

    Worked for me, thanks.

  2. ecarrera on

    @greg glad it helped!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: