Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Flat File read error retry? Expand / Collapse
Author
Message
Posted Monday, January 21, 2008 7:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
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!



Post #445456
Posted Tuesday, January 22, 2008 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 26, 2013 12:35 PM
Points: 61, Visits: 204
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
Post #445843
Posted Tuesday, January 22, 2008 6:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
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

Post #445850
Posted Tuesday, January 22, 2008 6:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:43 PM
Points: 50, Visits: 168
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.



Post #445857
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse