Determine if file is empty (SSIS)

Question

I am trying to develop a package in SSIS 2005 and part of my process is to check if a file on the network is empty or not. If it is not empty, I need to pass a status of successful, otherwise, I need to pass a status of unsuccessful. I think I need a script task, but am not sure how to go about it. Any help is appreciated.

Answer 1

Create a connection to the flat file in the Connection Managers panel. Under the Control flow tab, add a Data Flow Task.

Double click the Data flow task and add a Flat File Source and Row Count item.

In the Row Count properties, create a RowCount variable.

In the Control Flow tab, create control flow connections based on the result of the @RowCount.

Answer 2

There are two ways to do it:

If file empty means size = 0 you can create a Script Task to do the check: http://msdn.microsoft.com/en-us/library/ms345166.aspx

If My.Computer.FileSystem.FileExists("c:\myfile.txt") Then

  Dim myFileInfo As System.IO.FileInfo
  myFileInfo = My.Computer.FileSystem.GetFileInfo("c:\myfile.txt")

  If myFileInfo.Length = 0 Then
    Dts.Variables["Status"].Value = 0
  End If
End If

Otherwise, if file empty means no rows (flat file) you can use the a Row Count transformation after you reads the file. You can set a variable from the Row Count using the 'VariableName' property in Row Count editor and use it as a status.