When mainframes or partners are uploading a file, you don't want to process the file until you're certain the file is fully uploaded. One way to do this is with a Script Task in SSIS. There is no magic property to determine in the operating system to see if the file is in use though so you'll have to essentially try to trigger an error and then gracefully trap it and then set a variable that can read. In an SSIS Script Task, you can use similar VB.NET code as what you see below to do this:
| Public Sub Main()
'
Try
Dim path As String = Dts.Variables("sTraceFileName").Value
'If you want to detect first if the file exists, uncomment these lines. 'If File.Exists(path) = False Then
' File.Create(path)
'End If
File.SetLastAccessTime(path, New DateTime(1985, 5, 4)) Dts.Variables("bSafeToProcessTrace").Value = True Catch e As Exception Dts.Variables("bSafeToProcessTrace").Value = False End Try Dts.TaskResult = ScriptResults.Success
End Sub |
As you can see in the above code, I essentially tried to set the LastAccessedTime property via the method. If the file is in use, it will fail and trigger the bSafeToProcessTrace variable to be set to false. To use that variable, you can wire your next task to the Script Task as shown below. Then double-click on the green line and set an expression on the precedence constraint as shown below:
@bSafeToProcessTrace == True
Now, the data flow will only execute if the file is not in use. If you don't like to code, you can also download the File Properties Task from PragmaticWorks.com, which is a free task.
How Big Is Your Package?