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:\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:
- Open Visual Studio 2005.
- Click New Project in the toolbar.
- Click Business Intelligence Projects in the New Projects dialog box, then select Integration Services Project in the Templates list.
- Type NotepadFinder in the Name text box.
- 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:
- In the Package.dtsx tab, click the Control Flow tab.
- Right-click a blank area in the design surface, then click Variables.
- Add a variable named filePath and set its value to C:\WINDOWS\Notepad.exe, as Figure 1 shows.
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:
- With the Control Flow tab selected, click Toolbox on the View menu.
- Open the Control Flow Items section of the Toolbox and drag a Script task to the design surface.
- Right-click the Script task and click Edit.
- In the General section, click inside the Name box and change the name to Check if Notepad Exists.
- In the Description text box, replace the existing text with Checks to see if Notepad exists.
- Click the Script section.
- In the ReadOnlyVariables text box, type User::filePath, then click Design Script.
In the Microsoft Visual Studio for Applications window that appears, find the code
Imports Microsoft.SqlServer.Dts.RuntimeBelow it, add the line
- 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.
- Click Save on the File menu. Alternatively, you can press Ctrl+S on your keyboard.
- Close the Microsoft Visual Studio for Applications window by selecting Close and Return on the File menu.
In the Script Task Editor window, click OK to return to the designer.
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 (firstname.lastname@example.org) about whether or not the C:\WINDOWS\Notepad.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@example.com.
13. In the Subject field, enter SUCCESS: Notepad was found.
14. In the MessageSource field, type Notepad was found at C:\WINDOWS\Notepad.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:
- Name: Notepad Not Exists Notification
- Subject: ERROR: Notepad was NOT found.
- MessageSource: Notepad was NOT found at C:\WINDOWS\Notepad.exe. Check the package.
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.
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.Success
Dts.TaskResult = Dts.Results.Failure