how to check end of file in a csv file before processing it in ssis

Question

I have created an SSIS package which processes .CSV files using a ForEachLoop container.

All the csv files contains "END OF FILE" in the last row.

Only those CSV files will be processed if it contains "END OF FILE" in the last row.

How can it be done. Please help.

Thanks in advance.

You could add a Script task (within the ForEach Loop Container) to read the entire file in and set a flag if it finds the "END OF FILE" marker. Depending on the flag you would then go to the DFT or raise an error. — booyaa, Mar 28 '13 at 10:34

score 2 · Answer 1 · edited May 23 '17 at 12:04

Create a variable check

Name   DataType  Value
check  int        0

Let's say you have a package design like the one below

enter image description here

Script task is to check the file which has End of File at the last row

In the Script task add the variable check in ReadWriteVariable section and the output variable from ForEach container (suppose the variable name is LoopFiles) in ReadOnlyVariables

In the script task add the following code to read the file .There are several ways you can read the files here and here

 public void Main()
    {
     int counter = 0;
     string loop=  Dts.Variables["User::LoopFiles"].Value.ToString();
     string line;
      using (StreamReader files = new StreamReader(file))
         {
             while((line = files.ReadLine()) != null)
                {
                    if (line.ToLower() == "End Of File".ToLower())
                      { 
                          Dts.Variables["User::check"].Value = 1;
                       }
                }
         }
 Dts.TaskResult = (int)ScriptResults.Success;
 }

Double Click the green arrow connection script task and Data Flow Task .A precedence dialog box will open and enter the expression as below

enter image description here

score 0 · Answer 2 · answered Mar 28 '13 at 10:36

There are a number of ways that this could be done. One way would be:

Create the following variables:

EOF_Found Boolean

Row_Count Integer

Bring the data into a dataflow using the Flat File Source
Use a row count component to add the number of rows to Row_Count, to identify the last row later
Use a script component to loop through the rows, adding 1 to a counter for each row
When your counter equals the value in Row_Count (i.e. you are looking at the last row) check the value in the column that you expect "END OF FILE" to appear (depends on how you set up the flat file connection manager). if it equals "END OF FILE", change the value of EOF_Found to True
After the script component, add a derived column referencing the value in EOF_Found
Use a conditional split, checking the value of the derived column and only process if True

score 0 · Answer 3 · answered Mar 28 '13 at 22:27

This solution avoids reading the entire file line by line. I have merged Praveen's code here for sake of completeness.

    public void Main()
    {
        string line = ReadLastLine(@"c:\temp\EOF.cs");

         if (line.ToUpper() == "END OF FILE")
         { 
             Dts.Variables["User::check"].Value = 1;
         }

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

    public static string ReadLastLine(string path)
    {
        StreamReader stream = new StreamReader(path);
        string str = stream.ReadToEnd();
        int i = str.LastIndexOf('\n');
        string lastLine = str.Substring(i + 1);
        return lastLine;
    }

how to check end of file in a csv file before processing it in ssis

3 Answers3