Skip to main content

How to delete top n line from flat file using SSIS

Problem: Suppose we have a folder where the flat-file downloaded from any organization. Now we have to transfer data from flat file (comma separated file) to our database’s table but top n lines are the descriptions about the file, this thing may create a problem to access data from flat file to Table. File looks like below



Solution: If we remove the top n description line from the flat file, then we can efficiently process data from flat file to the database table.  
Steps -1: Hope you are aware of Flow Control and Data Flow
Step -2: Select tool Script Component


Step -3: Create a variable for file path and the number of lines to delete.


Step-4: Edit Script--> Custom Property--> Read Write Variable and add both users-defined variables.


Step -5: Click on the button Edit Script. A script file will be open. Add following namespace
using System.IO;
using System. Linq;

Step-6: Write down the following code.


    public override void PreExecute()
    {
        base.PreExecute();
      
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        string fPath = Variables.fPath;
        int deleteLines = Variables.lineNumber;
        string[] lines = File.ReadAllLines(fPath);
        lines = lines.Skip(deleteLines).ToArray();
        using (StreamWriter sr = new StreamWriter(fPath))
        {
            foreach (var v in lines)
            {
                sr.WriteLine(v);
            }
        }
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
     
         Output0Buffer.AddRow();
         Output0Buffer.MyColumn = 10;
       
    }



Step-7: After saving script run the package, you find the expected result.


Note: Above Script override the existing file. This tutorial is written for SSIS 2012.