Programmatically Checking If File Exits In SSIS
April 17th, 2012 / 6 Comments » / by admin
In ‘Generating Files through bcp Utility in SQL Server’ post I showed how to create a simple CSV file and output it onto a file system. In this post I will demonstrate how to programmatically check if the file exists in the directory where we intended to place it (it enables us to take action if the file has not been created as expected). This can be incorporated into a process of creating files via bcp utility as its extension; for example, if the file has not been created notify operator(s) or perform some other action.
Firstly, we will create a variable named FileExists (you can call it anything you want as long as you stick to the name throughout the process) of Boolean type, set the scope to ‘Package’ and set its value to FALSE. This variable will store our result and we will use it in a VB.NET script as well as precedence constraints.
Next, ensure that our FileExists variable has been declared in the ‘Script Task Editor’ for ‘ReadOnlyVariables’ field as per below and that the language selected is set to Microsoft Visual Basic 2008.
We are now ready to create a simple VB.NET script which populates the FileExists variable (setting to TRUE or FALSE) depending on whether the file that we wish to check for existence is there or not.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Dim directory As DirectoryInfo = New DirectoryInfo("c:\")
Dim file As FileInfo() = directory.GetFiles("*.csv")
If file.Length > 0 Then
Dts.Variables("User::FileExists").Value = True
Else
Dts.Variables("User::FileExists").Value = False
End If
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
End Class
All the above script does is checking if the file exists on the c:\ drive and then populating our FileExists SSIS package variable with either a value of ‘TRUE’ if it does and ‘FALSE’ if it doesn’t. This can then be used by controlling the flow of the subsequent package containers based on the logic applied in the precedence constraint which follows the script task. In order to redirect the flow, let’s create two dummy ‘Execute SQL Task’ containers, link them to our ‘Script Task’ and populate them with some dummy SQL code (two dashes i.e. — will do for this exercise). Next, we will apply a simple logic in the expression field of the two ‘Precedence Constraints’of to how to proceed with the execution. In the first one, set the ‘Evaluation Operation’ to Expression and type in the following expression in the ‘Expression’ field: @FileExists==TRUE as per below.
The second ‘Precedence Constraint’ will look almost identical with the exception of the actual expression. Here, we change it to @FileExists==FALSE. When finished, you should see two blue squares next to our ‘Precedence Constraints’ and the actual connections should change their color to blue as well (SQL Server 2008 R2). So far, the part of the package we have just developed should look like this.
To check if the package works, all we need to do is to put a CSV file in the target directory (c:\ drive in our case) and the execution should follow the path of the ‘Precedence Constraint’ where the expression was set to ‘@FileExists==TRUE’. If we delete the file, the execution should go with the other Precendence Constraint. Those two scenarios are pictured below.
This can be further developed by incorporating other tasks and actions e.g. if @FileExists==FALSE (meaning that no CSV file was found), ‘Send Mail Task’ can be run to notify operator(s).
To check if the package works, all we need to do is to put a CSV file in the target directory (c:\ drive in our case) and the execution should follow the path of the ‘Precedence Constraint’ where the expression was set to ‘@FileExists==TRUE’. If we delete the file, the execution should go with the other Precendence Constraint. Those two scenarios are pictured above.
