SSIS - How to Delete Top N Rows from CSV or Text File in SSIS by using Script Task

Scenario:

We have received text or csv file. The file has some company information,date created etc. before the data rows start. Our goal is to delete these information rows and regenerate file start from header row.
Fig: Source File With Company Information

Solution:

We will be using Script Task to Delete the Top N rows from text file. We will be reading all the data from the file in string type array and then overwrite the original file after removing Top N rows.

Step 1: 

Create two variables as shown in figure.
VarFilePath is variable holding the file path. VarDeleteTopNRows is variable that will hold the number of rows you want to delete from starting of file.
Fig 1: Create variables in SSIS Package To Delete Top N Rows from Flat File by using Script Task

Step 2:

Bring the Script Task to Control Flow Pane. Map the above Variables as shown to ReadOnlyVariables in Script Task.
Fig 2: Map Variables in Script Task

Paste the below Code. I have bold the code that I wrote, rest of the code is auto generated by 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 DeleteTopNRows = Convert.ToInt32(Dts.Variables["VarDeleteTopNRows"].Value); 
            string[] lines = System.IO.File.ReadAllLines(FilePath);
            lines = lines.Skip(DeleteTopNRows).ToArray();
            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
        };
        

    }
}


Save the code. Run the script task and once completed. Check the file again.
Fig 3: Top 3 Rows Deleted By SSIS Package.

You can change the value of variable VarDeleteTopNRows according to your requirements. 

28 comments:

  1. Saved as a favorite, I really like your site! Clicking Here: Top Five Funny Encryption Software Quotes & Here Are Five Ways To How To Encrypt A Password For Free Faster.

    ReplyDelete
  2. brother iam getting an exception,what should i do


    ReplyDelete
  3. Hello,
    I am very thankful for the effort put on by you, to help us, Thank you so much for the post it is very helpful, keep posting such type of articles.
    Mehndi Artist in Delhi | Best Mehndi Artist in Delhi | Simple Mehndi Design | Mehndi Service at Home in Delhi

    ReplyDelete
  4. Excellent post. This article is very helpful for people and also for me. I get some useful knowledge in this post. Thanks for sharing the informative topic, by the way... Digital Marketing Course in Delhi | Web Designing Course in Delhi | Graphic Designing Course in Delhi | English Speaking Course in Delhi | Data Science Course in Delhi | Machine Learning Course in Delhi | Python Course in Delhi

    ReplyDelete
  5. This post is very useful and informative. what is fantastic post, this is so chock full of useful information I cannot wait to dig deep and start utilizing the resource give me.your exuberance is refreshing.
    Digital Marketing Course in Pitampura | Digital Marketing Institute in Pitampura | Digital Marketing Training in Pitampura

    ReplyDelete
  6. Hello,
    These trends are so useful and beneficial for everyone, Thank you so much for this guide ...
    Have a Good Day...
    Digital Marketing Course in Janakpuri | | Digital Marketing Course in GTB Nagar

    ReplyDelete
  7. 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
  8. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.

    Best quoting software

    ReplyDelete
  9. It leaves me with some encoding error characters in my file after running the script task. not there before but there after?

    ReplyDelete
  10. So there is no place for a text qualifier so when converting an excel file to .csv, in my case, some rows had columns shifted and gave an unexpected result. do you know how to fix this?

    ReplyDelete
  11. So there is no place for a text qualifier so when converting an excel file to .csv, in my case, some rows had columns shifted and gave an unexpected result. do you know how to fix this?

    ReplyDelete
  12. Your work is very good and I appreciate you and hopping for some more informative posts. If you want to crack NEET Medical Exam in first attempt then Join YVS INSTITUTE Best NEET Coaching in Delhi

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Violin Shop Honolulu

    Are you finding online music classes for violin? Violin shop Honolulu offers online classes and face-to-face classes at most at an affordable price. We are giving online classes on face time, Skype, zoom, and Hangout. Get in touch with us at www.honolulumusiclessons.com.

    ReplyDelete
  15. Ziyyara Edutech is a worldwide online home tuition provider. Our main focus is not only to help students to study, but also to support them to get maximum marks and prepare them in a manner that they can clear their exams with flying colors. Call us @ +91 9654271931

    ReplyDelete
  16. Thanks for the information. I really like the way you express complex topics in lucid way. It really helps me understand it much better way. kailua-kona homes for sale

    ReplyDelete