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()

    /// <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)
         * Add your code here

    public override void CreateNewOutputRows()
         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.