January 21, 2008 at 7:09 am
I am trying to read a flat file in SSIS. If the file is currently locked, I want to be able to wait one second, and try again. I want to continue retrying until the lock is gone.
What is the best way to do this?
Thanks!
January 22, 2008 at 6:06 am
Hi,
you could use a scripttask which checks the state of your input file, and if its locked waits for e.g. 1 second.
To prevent the package from running into a deadlock you should limit the number of retries, this could be done using a loop.
Regards,
Jan
January 22, 2008 at 6:32 am
On the Control Flow canvas add a Script Task and connect that to the Data Flow task.
In the following example the Script Task loops until the file becomes available or a timeout period has expired.
' VB.Net
' Returns success if file can be opened before a timeout period has expired
' Note: Add Imports System.IO at beginning
'
' Replace hardcoded file name with package variable.
Dim strFilename As String = "c:\temp\input.csv"
Dim fsMyFile As System.IO.FileStream
Dim bFinished As Boolean = False
Dim iCount As Integer = 1
dim iTimeout as Integer = 60
Do
Try
fsMyFile = System.IO.File.Open(strFilename,FileMode.Open,FileAccess.ReadWrite,FileShare.None)
bFinished = True
fsMyFile.Close()
Catch ioex As System.IO.IOException
iCount += 1
System.Threading.Thread.Sleep(1000)
End Try
Loop Until bFinished Or iCount > iTimeOut
If bFinished Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
January 22, 2008 at 6:56 am
Thank you both! I just implemented this script and it works perfect!
Thank you SQLServerCentral.com for this forum to allow users to help each other.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply