Delete rows from an Excel file using SSIS

1. Bring the Script Task in the Control Flow tab.

2. Open the Script task and open the edit window.

3. It will open the visual studio for C# language to coding. having line to add your code as shown below:

4. Now you use your code to delete the line or lines from the excel file:

// Add Namespace:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

public void Main()
{
        // TODO: Add your code here
        Excel.Application app = new Excel.Application();
        app.Visible = false;
        Excel.Workbook workbook = app.Workbooks.Open(@"C:\SSISoutput\Project.xlsx");



    //Open the sheet 1 in workbook
        Excel.Worksheet worksheet = workbook.Worksheets[1];
    //Delete the rows from 2 to 5 as follows: (means 4 rows deleted)
        worksheet.Rows[2].Delete();
        worksheet.Rows[2].Delete();
        worksheet.Rows[2].Delete();
        worksheet.Rows[2].Delete();

       //Save the workbook and close it

            workbook.Save();
            workbook.Close();
            app.Application.Quit();
            Dts.TaskResult = (int)ScriptResults.Success;

}

Note: Use row no 2 i.e worksheet.Rows[2].Delete(); every time because 3 row move to 2 position.

5. Build and exit the script:

6. Run the SSIS package will delete the mentioned line in excel.

IF you want to delete more lines you can use for loop in code to delete multiple lines at once example:

// Add Namespace:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;


public void Main()
		{
       // TODO: Add your code here
       Excel.Application app = new Excel.Application();
       app.Visible = false;
       Excel.Workbook workbook = app.Workbooks.Open(@"C:\SSISoutput\ Project.xlsx");

Excel.Worksheet worksheet = workbook.Worksheets[1];
            
// delete from 2 row and delete 100  next lines  
            for (int i = 2; i < 100; i++)
            {
            worksheet.Rows[2].Delete();
            }

            workbook.Save();
            workbook.Close();
            app.Application.Quit();
            Dts.TaskResult = (int)ScriptResults.Success;

		}