CODEWITHSUNDEEP
sql-server-2005ssis
DoubleJ92
DoubleJ92

Reputation: 303

Determine if file is empty (SSIS)

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.

Upvotes: 5

Views: 18049

Answers (4)

AMouat
AMouat

Reputation: 755

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.

Upvotes: 1

grapefruitmoon
grapefruitmoon

Reputation: 3008

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

Upvotes: 0

pcofre
pcofre

Reputation: 4066

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.

Upvotes: 3

jim31415
jim31415

Reputation: 8808

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

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

In the Row Count properties, create a RowCount variable. enter image description here

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

Upvotes: 12

Related Questions