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. 

35 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. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. We are extremely thankful for your blog entry.
    Best Singing Classes in Delhi | Fashion Designing Institute in Delhi | Makeup Courses in Delhi | Event Management Institute in Delhi | Interior Design Course in Delhi

    ReplyDelete
  6. Nice blogs
    Great Information.
    #makeupCourse #NutritionCourse #HairCourse #SpaCourse #CosmetologyCourse #NailCourse #AestheticsSkinCourse
    makeup Course

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. 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
  11. It leaves me with some encoding error characters in my file after running the script task. not there before but there after?

    ReplyDelete
  12. 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
  13. 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
  14. Thank you for sharing this amazing info.
    If you want to do Ukulele lessons Honolulu ? Honolulu guitar lessons offer different ukulele classes for learners. We are offering guitar classes at reasonable prices and learn music online with us. You can check our website to get more details and contact us through the Call or email for classes.

    ReplyDelete
  15. Thank you for sharing this amazing info.
    If are you looking for a home renovation or any other construction work then Graham Builders offers home remodeling with the latest designs. A Graham builder is an award-winning company and provides construction works for decades.
    If anyone wants detail info click Hawaii general contractor . For more info, you can check our website and contact us through it.

    ReplyDelete
  16. Thank you for sharing this amazing info.
    Do you want to study Voice lessons Honolulu ? Honolulu music lessons give several kinds of Voice lessons to music lovers. Our Workshop offers classes online and offline so everyone will comfortable with learning. Short-time good educations are the best combo for learners.

    ReplyDelete
  17. Thank you For Share Your Information.
    Dolphin Discoveries & Ocean Adventures provide the best Kona snorkel trips, as well as cultural, and environmental information relevant to the island of Hawaii and its history. Along with the snorkeling and history, your captain will show you several spectacular, unique volcanic geological phenomena including sea caves and lava tubes.

    ReplyDelete
  18. 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
  19. This comment has been removed by the author.

    ReplyDelete
  20. 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
  21. 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
  22. 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