A Simple But Effective Way to Tell Whether a File Exists Using an SSIS Package

SQL Server Integration Services (SSIS) packages often need to access a data file on the computer’s hard disk. If the file doesn’t exist on the hard disk, SSIS throws an error and the package fails. The failure can be particularly problematic if you’re running the SSIS package in a production environment in which you have limited access and control. That’s the case where I work. I have to wait 24 hours or more (depending on the deployment calendar) from when a package fails to when I can diagnose and fix the problem. Having a package fail because it couldn’t find a file means the package’s implementation is set back a day or more.

Related: SSIS Package Pings Servers

To avoid such delays, I created an SSIS package that checks whether a specified file exists, then sends me an email letting me know what it found. If the file exists, I run the SSIS package that uses it. If the file doesn’t exist, I fix the problem.

Building a package that checks for files isn’t difficult. It involves creating a blank package, adding variables to the package, adding Script tasks to the package, and adding Send Mail tasks to the package. I’ll walk you through building a sample package that checks to see whether the file C:WINDOWSNotepad.exe exists. For this example, I’m using SQL Server 2005 with SSIS and Business Intelligence Development Studio (BIDS) installed and Microsoft Visual Studio 2005 SP1 (for backward compatibility).

Creating the SSIS Package

You first need to create a blank SSIS package. To do so, follow these steps:

Visual Studio 2005 opens a new solution and creates a blank DTS package for you.

Adding the Variables

Next, you need to add a variable for each file you want the package to search for. The variables are used to store the files’ pathnames. For this example, only one variable is needed. To add it, follow these steps:

Figure 1: The filePath variable

Note that if you need to add more than one variable, be sure to give the variables distinctive names so you can easily keep track of which variable refers to which file.

Adding the Script Tasks

At this point, you need to add Script tasks that search for the specified files. Each file needs its own Script task. For this example, do the following:

Adding the Send Mail Tasks

It’s now time to add the Send Mail tasks, which will be used to send email notifications. You can add up to two Send Mail tasks for each Script task. At the least, you’ll probably want to notify yourself or the systems administrator when the file isn’t found. Optionally, you can also add a Send Mail task when the file is found. For this example, an SMTP server (smtp.server.com) will be used to notify the administrator ([email protected]) about whether or not the C:WINDOWSNotepad.exe file exists. Here are the steps to take:

1.     Open Control Flow Items in the Toolbox and drag two Send Mail tasks to the design surface.

2.     Connect the Script task to both Send Mail tasks. Green arrows, which are called precedence constraints, will appear, showing they’re connected.

3.     Right-click one of the green precedence constraints and select Failure. It will turn red. This red precedence constraint is taken when the Script task returns Dts.Results.Failure. The green precedence constraint is taken when the Script task returns Dts.Results.Success.

4.     Go down to the Connection Managers tab below the design surface and click New Connection.

5.     In the Connection manager type list, click SMTP, then Add.

6.     In the SMTP Connection Manager Editor, enter smtp.server.com in the Name text box. Click OK. BIDS will add a new SMTP connection manager to the Connection Managers tab, giving it the same name as the SMTP server you specified. So, for this example, the connection manager’s name is smtp.server.com.

7.     Right-click the Send Mail task that’s connected with the green precedence constraint and click Edit.

8.     In the Name box, type Notepad Exists Notification.

9.     In the Description box, type Sends an email notifying admin that Notepad exists.

10.  Click Mail in the left-hand pane.

11.  In the SmtpConnection drop-down box, select smtp.server.com.

12.  In the To field, type [email protected].

13.  In the Subject field, enter SUCCESS: Notepad was found.

14.  In the MessageSource field, type Notepad was found at C:WINDOWSNotepad.exe. Click OK.

15.  Right-click the Send Mail task that’s connected with the red precedence constraint and click Edit.

16.  Configure the Send Mail task following the instructions in steps 8 through 14, except use these values:

When you’re done, the design surface in the Control Flow tab should look like that in Figure 2.

17.  Save the package by clicking the Save All button on the toolbar or the Save All option on the File menu.

Figure 2: The end result in the Control Flow tab

A Simple But Effective Package

As you’ve seen, it’s fairly simple to create an SSIS package that checks and notifies you about whether a specified file exists. If you’d like to see the NotepadFinder package I demonstrated here, you can download it by going to the top of this page and clicking theF 129915.zip hotlink. To test the package in your environment, you just need to customize Notepad’s pathname, the SMTP server settings, and the email addresses and the messages. (If you don’t know the SMTP server settings, ask your IT administrator.) You can even adapt it to check for a different file if desired.

Related: Use Checkpoints to Restart Failed SSIS Packages
 

Listing 1: The New ScriptMain Class

Public Class ScriptMain  Public Sub Main()If (File.Exists(CStr(Dts.Variables("filePath").Value))) Then  Dts.TaskResult = Dts.Results.SuccessElse  Dts.TaskResult = Dts.Results.FailureEnd If  End SubEnd Class