This is a very common scenario in SSIS where you want to check if source file exists. If it does then move it to certain folder otherwise Wait and check again.
You can do this using a script task and the following code
This is how you can configure your script task
Configure the READONLY and READWRITE variables
And in the script Put this code
‘ Microsoft SQL Server Integration Services Script Task
‘ Write scripts using Microsoft Visual Basic
‘ The ScriptMain class is the entry point of the Script Task.
Public Class ScriptMain
‘ The execution engine calls this method when the task executes.
‘ To access the object model, use the Dts object. Connections, variables, events,
‘ and logging features are available as static members of the Dts class.
‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
‘ To open Code and Text Editor Help, press F1.
‘ To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
‘ Add your code here
Dts.Variables("source_full_file_path").Value = New StringBuilder(Dts.Variables("source_folder_path").Value.ToString).Append("\").Append(Dts.Variables("source_file_name").Value.ToString).ToString()
Dts.TaskResult = Dts.Results.Success
If (File.Exists(Dts.Variables("source_full_file_path").Value.ToString()) And IsFileLocked(Dts.Variables("source_full_file_path").Value.ToString()) = False) Then
Dts.Variables("file_exists").Value = True
Dts.Variables("file_exists").Value = False
‘MsgBox("file path: " + Dts.Variables("source_full_file_path").Value.ToString())
‘MsgBox("File Exists" + Dts.Variables("file_exists").Value.ToString())
Public Function IsFileLocked(ByVal filename As String) As Boolean
Dim Locked As Boolean = False
Dim fs As FileStream = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.None)
Catch ex As IOException
Locked = True
You can see that the last fuction IsFieLocked() is also there to avoid scernarios where file does exists but it is being written into by the extract from source. When this is happening you don’t want to try to move this file. Once the write Lock is release you can copy it.
After this Step you can do the file move in the same script task or you can use file system task to move/copy the file.
I have decided to use the file system task and below is how you can configure it .
Hope this helps someone