Verifying File Existence With SSIS
At some point, you will undoubtedly run into a request where you need to determine whether a file exists before taking action on it. One way to address this requirement is through the use of a script task.
Step 1 – Add a Script Task
Drag a Script Task onto the package canvas.
Step 2 – Create the Variables for Usage with the Script Task
Create three variables for the script task to use.
Name Scope Datatype Value
vFilename Package String Filename and extension
vFilePath Package String C:\path to your file\
vFileExistenceFlag Package Int32 0
The file path and file name are divided between two variables. The vExistenceFlag variable serves the purpose of capturing the value returned by the script task. The script task will return 1 if the file exists and return 0 if the file does not exist.
Step 3 – Configure the Script Task to Use the Variables Defined Prior Step
Identify the variables that are accessible to the script task. There are two types of variables: ReadOnlyVariables and ReadWriteVariables.
• ReadOnlyVariables can only be read by the script task.
• ReadWriteVariables can be read and modified by the script task.
Click on the ellipses for each variable type to assign the variable values.
This action will display the “Select Variables” window. Check the box for the vFilename and vFilePath variables. Each of the variables should be prefaced by “User”. Click on “OK” after making your selections.
Next, identify the ReadWriteVariables. Click on the ellipses for this variable type to identify the variable to be used.
This will display the “Select Variables” window. Check the box for the vExistenceFlag variable and click on the “OK” button.
Your result should look similar to the image below.
Step 4 – Edit the Script for the Script Task.
In this step, we will be populating the script for the script task. Before we begin editing the script, verify that Microsoft Visual C# is selected as the scripting language. Microsoft Visual Basic is also an option, but we will be using Microsoft Visual C# for this tutorial.
Click on the Edit Script button.
This action will open the script editor window.
The default namespaces are System, System.Data, Microsoft.SqlServer.Dts.Runtime, and System.Windows.Forms. You will need to add the System.IO namespace. Your namespaces should resemble the following:
#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
Insert the following script into the main() function. You can copy and paste the following code within the main() function.
try
{
// filePathName = This is the variable that provides the fully qualified
// path to the file.
String filePathName = Dts.Variables["User::vFilePath"].Value.ToString() + Dts.Variables["User::vFilename"].Value.ToString();
if (
//File.Exists(string)- This method will determine whether or not the
//file exists.
//It returns True if the file exists, False if it does not exist.
File.Exists(filePathName))
{
//Return a value of True to SSIS
Dts.Variables["User::vExistenceFlag"].Value = 1;//File Exists
}
else
//Return a value of False to SSIS
Dts.Variables["User::vExistenceFlag"].Value = 0;//File does not exist
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
The script should resemble the script below once complete.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
namespace ST_ad2969d5e1454423bc8cba159ad76a2f
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
try
{
// filePathName = This is the variable that provides the absolute path to the file.
String filePathName = Dts.Variables["User::vFilePath"].Value.ToString() + Dts.Variables["User::vFilename"].Value.ToString();
if (
//File.Exists(string)- This method will determine whether or not the file exists.
//It returns True if the file exists, False if it does not exist.
File.Exists(filePathName))
{
//Return a value of True to SSIS
Dts.Variables["User::vExistenceFlag"].Value = 1; //File Exists
}
else
//Return a value of False to SSIS
Dts.Variables["User::vExistenceFlag"].Value = 0; //File does not exist.
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Save your changes and close out of the script editor. Click the “OK” button on the subsequent Script Task Editor Window. The script task is now complete.
Step 5 – Identify Actions to Take after Testing for File Existence
We now need to identify what actions should take place if:
• The file exists
• The file does not exist
We will be using precedence constraints (the connectors between tasks) and two script tasks to test this proof on concept. In reality, you can use almost any task you wish. I normally use a File System task after checking for a file’s existence, but you can use any task as determined by your requirements.
Drag two script tasks onto the package canvass and connect them to the File Existence Script Task using precedent constraints. I named each task to demonstrate which task should be executed based on whether or not the file myFile.txt exists. I simply placed the following statements in each of the following script tasks.
ST-File Exists: MessageBox.Show(“File exists.”);
ST-File Does Not Exist : MessageBox.Show(“File does not exist.”);
Double click on the constraint leading to the task that will be executed if the file is found. This action will open the Precedence Constraint Editor.
• Select “Expression and Constraint” as the Evaluation operation.
• Ensure that “Success” is the option selected for Value.
• Click on the ellipses to create the expression.
After clicking on the ellipses you will see the Expression Builder window. You can build the expression by dragging the fileExistsFlag variable to the Expression window and adding the test logic.
• @[User::vExistenceFlag]==1
The result should look like the image below.
After entering the test logic click on the “OK” button. Click on “OK” on the subsequent window. This completes the configuration for the first precedence constraint. Your results will look similar to the following image.
Double click on the constraint leading to the task that will be executed if the file is not found. This action will open the Precedence Constraint Editor.
• Select “Expression and Constraint” as the Evaluation operation.
• Ensure that “Success” is the option selected for Value.
• Click on the ellipses to create the expression.
After clicking on the ellipses you will see the Expression Builder window. You can build the expression by dragging the fileExistsFlag variable to the Expression window and adding the test logic.
• @[User::fileExistsFlag]==0
The result should resemble the image below.
After entering the test logic, click on the “OK” button. Click on “OK” on the subsequent window. This completes the configuration for the first precedence constraint. Your results will resemble the following image. Notice the precedence constraints now have fx notation.This notation identifies that the element uses an expression.
Step 6 - Testing the Package
Next, we need to test the package.
• Create a folder on you C:\ drive.
• Create a folder named Test
• Create the following file C:\Test\myFile.txt
After creating the file in the specified location, execute the package. Upon successful completion, you should see that the correct task is executed, verifying file existence, signified by message box display.
Next, delete the file myFile.txt, and rerun the package. Upon successful completion, you should see that the correct task is executed, verifying the file does not exist signified by message box display.
You now have a fully functional script task to check for a file's existence. This was test to demonstrate how to check for file existence before taking action on the file. You can connect any task to the script task, such as a file system task to archive the file, a data flow task to process the file, or a send mail task to transmit the file. The action you take is based on your requirements.