Situation
I have a package with an execute SQL task that truncates the destination table as the first step in the control flow and a data flow task that reads data from a flat file and loads a sql server table.
Once in a while the package bombs because it cannot get access to the flat file. The end result is that the table is empty because the truncate runs first. Obviously, I need to address the file contention, but I was wondering how to address this issue in general since anything that causes the data flow to blow up would leave the table empty.
I would rather have the table with day old data than empty, since it is not mission critical and the users can at least look at yesterday's data as opposed to nothing.
Question
Is there a way to specify a "load replace" on the OLEDB destination? I haven't seen one and I guess it makes sense because the data flow task transformations run row by row.
The only solution that I have come up with is to have the following on the control flow:
1) data flow task which reads flat file and loads a temp table
2) execute sql task to truncate the "real" destination
3) data flow task to move data from temp table to real table.
Anyone else come up with a better way to handle this?
Thanks!!
I guess from my perspective, I'd never truncate a table as the "first" step of an import process. You're guaranteed to have upset users when an issue that's completely out of your control (meaning, the missing flat file) asserts itself.
I'm using the following logic in a similar application:
1) In a Script Task, I verify the flat file is available, and contains at least one row of data.
2) If the "flat file verification" succeeds, a "Go_NoGo" boolean variable is set to True; otherwise, it remains False
3) An expression (@.[User::Go_NoGo]==True) on the constraint between the Script Task and the next task in the control flow effectively prevents the truncate / replace operation from running if the variable is False.
The code to verify the existance of the flat file is pretty simple:
Private Function GetFileExists(ByVal ImportFile_Path As String) As Boolean
Dim fi As System.IO.FileInfo
Try
fi = New System.IO.FileInfo(ImportFile_Path)
If Not fi.Exists Then
'No point in continuing - cannot find file:
'Discard FileInfo object:
fi = Nothing
Return False
End If
If fi.Length = 0 Then
fi = Nothing
Return False
End If
'Discard FileInfo object:
fi = Nothing
Return True
Catch ex As Exception
Throw ex
End Try
End Function 'GetFileExists
The FileInfo "Length" property tells you how big the flat file is, so it's a pretty good measure of whether or not the flat file contains data.
Obviously, the two If statement can be combined with an Or. In my application I actually check the count of lines in the flat file, and save it to make sure I import all the rows by comparing the line count with the row count in the destination table:
Private Function GetLineCount(ByVal ImportFile_Path As String) As Long
Dim LineCount As Long
Dim sr As System.IO.StreamReader
Dim str As String
Dim i As Long
Try
sr = New System.IO.StreamReader(ImportFile_Path)
Do
str = sr.ReadLine
If Not str Is Nothing Then
If str.Length > 1 Then
LineCount += 1
End If
str = String.Empty
End If
Loop Until str Is Nothing
'Discard StreamReader object:
sr.Close()
sr = Nothing
Return LineCount
Catch ex As Exception
Throw ex
End Try
End Function
I don't run the GetLineCount function until I know the file is there and is more than zero length.
|||
Interesting approach....I may add something similar to my process.
Thanks for taking the time to provide an answer!
Jamie Thomson wrote:
lwulfers wrote: