SSIS - Copy file when Exists and Not Locked by another process

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.

 

Imports System

Imports System.Text

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

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

        ‘

        System.Threading.Thread.Sleep(1000)

        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

        Else

            Dts.Variables("file_exists").Value = False

        End If

        ‘MsgBox(Dts.Variables("file_exists").Value)

        ‘MsgBox("file path: " + Dts.Variables("source_full_file_path").Value.ToString())

        ‘MsgBox("File Exists" + Dts.Variables("file_exists").Value.ToString())

 

    End Sub

    Public Function IsFileLocked(ByVal filename As String) As Boolean

        Dim Locked As Boolean = False

        Try

            Dim fs As FileStream = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.None)

            fs.Close()

        Catch ex As IOException

            Locked = True

        End Try

        Return Locked

    End Function

End Class

 

 

 

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

 

About these ads