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.

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:\WINDOWS\Notepad.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:

1. Open Visual Studio 2005.
2. Click New Project in the toolbar.
3. Click Business Intelligence Projects in the New Projects dialog box, then select Integration Services Project in the Templates list.
4. Type NotepadFinder in the Name text box.
5. Click OK.

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:

1. In the Package.dtsx tab, click the Control Flow tab.
2. Right-click a blank area in the design surface, then click Variables.
3. Add a variable named filePath and set its value to C:\WINDOWS\Notepad.exe, as Figure 1 shows.

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:

1. With the Control Flow tab selected, click Toolbox on the View menu.
2. Open the Control Flow Items section of the Toolbox and drag a Script task to the design surface.
3. Right-click the Script task and click Edit.
4. In the General section, click inside the Name box and change the name to Check if Notepad Exists.
5. In the Description text box, replace the existing text with Checks to see if Notepad exists.
6. Click the Script section.
7. In the ReadOnlyVariables text box, type User::filePath, then click Design Script.
8. In the Microsoft Visual Studio for Applications window that appears, find the code
   
   

   Imports Microsoft.SqlServer.Dts.Runtime

   Below it, add the line

   Imports System.IO

9. Change the ScriptMain class to match the code in Listing 1. This code checks to see whether the file specified by the filePath variable exists. If it exists, the Script task returns the result of Dts.Results.Success. Otherwise, the Script task returns the result of Dts.Results.Failure.
10. Click Save on the File menu. Alternatively, you can press Ctrl+S on your keyboard.
11. Close the Microsoft Visual Studio for Applications window by selecting Close and Return on the File menu.
12. In the Script Task Editor window, click OK to return to the designer.