Posts Tagged ‘check file exists using ssis’

In control flow window, right click and choose variables.

Then define three variables FilePath, FileName and IfFileExists of string,string and Boolean type respectively.

In same window drag one script task and two Execute SQL Tasks.

Open script task, make variables FilePath and FileName as read only variables .

IfFileExists variable as read write variable like below

Now click on design script and use below code

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim FLName, FName As String

If Dts.Variables.Contains(“User::FilePath”) = True AndAlso _

Dts.Variables.Contains(“User::FileName”) = True Then

FLName = CStr(Dts.Variables(“User::FilePath”).Value)

FName = CStr(Dts.Variables.Item(“User::FileName”).Value)

If File.Exists(FLName + FName) Then

Dts.Variables.Item(“User::IfFileExists”).Value = True


Dts.Variables.Item(“User::IfFileExists”).Value = False

End If

Dts.TaskResult = Dts.Results.Success


Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

Then set precedence constraint based on IfFileExists like below against both Execute SQL tasks

@[IfFileExists]==TRUE in one and @[IfFileExists]!=TRUE in other one

Finally, it looks like below:

Read Full Post »

%d bloggers like this: