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

Yes, a Script task will do the job here. Add a using System.IO statement to the top of the script, then something along the lines of the following in the Main method will check the contents of the file.

        public void Main()
    {
        String FilePath = Dts.Variables["User::FilePath"].Value.ToString();

        String strContents;
        StreamReader sReader;
        sReader = File.OpenText(FilePath);
        strContents = sReader.ReadToEnd();
        sReader.Close();
        if (strContents.Length==0)
            MessageBox.Show("Empty file");

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Edit: VB.Net version for 2005...

    Public Sub Main()

    Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString()
    Dim strContents As String
    Dim sReader As StreamReader

    sReader = File.OpenText(FilePath)
    strContents = sReader.ReadToEnd()
    sReader.Close()
    If strContents.Length = 0 Then
        MessageBox.Show("Empty file")
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

Answer 3

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.

Answer 4

Add a simple Script Task with the following code(C#) should do the trick:

String FilePath = (string)Dts.Variables["User::FilePath"].Value;

var length = new System.IO.FileInfo(FilePath).Length;

if (length == 0)
    Dts.TaskResult = (int)ScriptResults.Success;
else
    Dts.TaskResult = (int)ScriptResults.Failure;

This option will run a lot quicker than the accepted answer as it doesn't need to read the whole file, if you are cycling through a folder of files and some of them are large, in my case ~800mb, the accepted answer would take ages to run, this solution runs in seconds.