Jul 13, 2016

How to delete top n line from flat file using SSIS

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












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













Step -3: Create variable for file path and number of line to delete.


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


















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

Step-6: Write down 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 expected result.








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