SSIS - How To Delete Bottom N Rows From Flat File in SSIS Package

Scenario:

In our previous post, we learnt how to delete Top N rows from flat file by using SSIS. In this post we will learn how to delete Bottom or Last N rows from text file in SSIS by using Script task. In below fig we can see that there are four rows those are company information and file information. We want to delete these rows before use this file as source to load into destination.
fig 1: Source file with extra information that need to deleted.


Solution:

We will be using Script Task in SSIS Package to delete bottom N rows from text file. Here are the steps.

Step 1:

Create SSIS Package and inside SSIS Package create two variables. One to hold  source file path and other to hold the rows to be deleted from bottom.
Fig 2: Create variables in Delete Bottom N Rows SSIS Package

Step 2:

Bring the Script Task to Control Flow Pane and then map the variables as shown in fig 3.
Fig 3: Map the variables in Script Task for Delete Last N Rows from Flat File

Click on Edit Script button and then paste the below script. I have bold the script that I wrote. You can copy and paste only bold code to your Script Task.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Linq;


#endregion

namespace ST_c62f3dcfb0964917aade179aac4edfab
{
   
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
       
        public void Main()
        {
            // TODO: Add your code here
    string FilePath = Dts.Variables["User::VarFilePath"].Value.ToString();
    Int32 DeleteBottomNRows = Convert.ToInt32(Dts.Variables["VarDeleteBottomNRows"].Value); 
            string[] lines = System.IO.File.ReadAllLines(FilePath);
            
           Array.Reverse(lines);
           lines= lines.Skip(DeleteBottomNRows).ToArray();
           Array.Reverse(lines);

            System.IO.StreamWriter file = new System.IO.StreamWriter(FilePath);

            foreach (string line in lines)
            {
            // MessageBox.Show(line.ToString());
            file.WriteLine(line);
            }

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

     
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        

    }
}


Let's run our SSIS Package now and check the file if required rows are deleted from bottom.

Fig 4:Bottom N Rows are deleted from Flat File by using SSIS Package.

As we can see that the required rows are deleted from source file. We are reading the file in string array and then deleting the required rows and over writing the actual source file. The number of rows can be changed by using the value of VarDeleteBottomNRows variable.





7 comments:

  1. Hello,

    How well does this task perform on a 1M+ file? I need to do this exact task (and I already am using a solution that reads the entire string into memory and then re-writes but takes an hour+ for this task alone) on a 100MB file. Is there a way to optimize this or another way you can think?

    ReplyDelete
  2. How do I execute this script by deleting the bottom rows based on a specific text value "Failure"?
    So if the file contains failure in one of the lines, I want to delete the last 5 lines from the file.

    ReplyDelete
  3. I am very happy to read the above information. It is really helpful for me, I learned many new things from your website. Big thanks to you all for everything and I am really very happy.
    What Is Digital Marketing | Digital Marketing Career Opportunities | Career in Digital Marketing | Digital Marketer Skills

    ReplyDelete
  4. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql server dba online training
    SQL dba online course

    ReplyDelete