Delete rows from an Excel File using SSIS Script Task

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;

		}

1 thought on “Delete rows from an Excel File using SSIS Script Task

  1. Pingback: Error during excel destination column is small in SSIS | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.