A common data source for my packages includes flat files. Unlike the scheduling of the master package, however, the flat files may not be staged every single day. Leaving the packages responsible for loading the files as is, however, will result in a false failure. This leads to the following script task in the control flow. The task will read input variables, which the file connection manager should also be using, to test whether the file exists or not. If it does not, it will not execute the subsequent data flow and return a success.
Defined variables:
ExecDir
FileExists
FileName
Defined Data Flow:
Script Task
In the Control Flow, add a Script Task. The task is defined with ReadOnlyVariables of:
User::ExecDir
User::FileName
The ReadWriteVariables is set to:
User::ExecDir
Add the following code to the script task:
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
‘ Created: Zack Bethem – AmberLeaf
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains(“User::ExecDir”) = True AndAlso _
Dts.Variables.Contains(“User::FileName”) = True Then
fileLoc = CStr(Dts.Variables(“User::ExecDir”).Value)
fileName = CStr(Dts.Variables.Item(“User::FileName”).Value)
‘System.Windows.Forms.MessageBox.Show(“FileDir:”+fileLoc+”FileName:” + fileName)
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item(“User::FileExists”).Value = True
‘System.Windows.Forms.MessageBox.Show(“File exists”)
Else
Dts.Variables.Item(“User::FileExists”).Value = False
‘System.Windows.Forms.MessageBox.Show(“File not exists”)
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
Now your set. The script task will read in the variables User::ExecDir and User::FileName. Using those variables, it will use the System.IO to determine if the file exists or not. If it does, the variable User::FileExist variable is set to True. Otherwise, the variable is set to False.
Conditions in Control Flow
The next step is to define a condition in the control flow. This is done by double clicking on the path line from the Script task to the Data Flow task. This leads to the following dialog:
Here is where you add the conditional logic. The Evaluation Operation is set to ‘Expression’. Using our User::FileExists variable, a check is done to see if the value is equal to True. If it is, the data flow executes. Otherwise, we have our graceful exit and the package is successful.
