Wednesday, May 25, 2011

Flat File Existence Check in SSIS

In Microsoft Visual Basic 2008:

Need to declare 2 variables
bolFileExists Boolean type
FlatFilePath string type (to store the flat file path)

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="") _
System.CLSCompliantAttribute(False) _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()

Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::FlatFilePath") = True Then
fileLoc = CStr(Dts.Variables("User::FlatFilePath").Value)
fileName = "FolderName\TxtFileName.txt"
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::bolFileExists").Value = True
Dts.Variables.Item("User::bolFileExists").Value = False
End If
Dts.TaskResult = ScriptResults.Success
Dts.TaskResult = ScriptResults.Failure
End If
End Sub

End Class