Wednesday, April 11, 2012

How to Check File Existance in the SSIS package


SET
--Read only Variables:
--User::SourceFileExtension,User::SourceFileName,User::SourceFilePath
--ReadWrite Variables:
--User::bolFileExists






' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.


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


_
_
 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, fileExtension, FileDate As String
        If Dts.Variables.Contains("User::SourceFilePath") = True AndAlso _
           Dts.Variables.Contains("User::SourceFileName") = True AndAlso _
            Dts.Variables.Contains("User::SourceFileExtension") = True Then
            fileLoc = CStr(Dts.Variables("User::SourceFilePath").Value)
            fileName = CStr(Dts.Variables("User::SourceFileName").Value)
            FileDate = Now.Date.ToString("yyyy") & Now.Date.ToString("MM") & Now.Date.ToString("dd")
            fileExtension = CStr(Dts.Variables("User::SourceFileExtension").Value)
            If File.Exists(fileLoc + fileName + FileDate + fileExtension) Then
                Dts.Variables.Item("User::bolFileExists").Value = True
                Dts.TaskResult = ScriptResults.Success
            Else
                Dts.Variables.Item("User::bolFileExists").Value = False
                Dts.TaskResult = ScriptResults.Failure
            End If
            'Dts.TaskResult = ScriptResults.Success
            ' Else
            ' Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub


End Class