Determine if the file is empty (SSIS)

Question

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

Answer 1

Yes, the Script task will do the job here. Add using the System.IO statement to the beginning of the script, then something according to the lines of the following in the Main method checks 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 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 2

Create a flat file connection in the Connection Managers panel. On the Flow Control tab, add the Data Flow task.

Double-click the Data Stream task and add the Flat File and Number of Rows items.

In the Row Count properties, create the RowCount variable.

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

Answer 3

There are two ways to do this:

If an empty file means size = 0, you can create a Script task to 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 an empty file means there are no lines (flat file), you can use the conversion of the line graph after you read the file. You can set a variable from a Row string using the VariableName property in the string editor and use it as a status.

Answer 4

Add a simple Script task with the following code (C #) to 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 parameter will work much faster than the accepted answer, since it does not need to read the entire file, if you move around the folder with files, and some of them are large, in my case ~ 800mb, the accepted answer will take a lot of time, this solution works in seconds.