How to automatically refresh PivotTable in excel

Below is the C# code to refresh the pivottable in excel workbook

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Microsoft.Office.Interop.Excel;

--------------------

public void Main()
{
// TODO: Add your code here
            string path = Dts.Variables["$Project::RefreshExcel_Path"].Value.ToString();
            string filename = Dts.Variables["$Project::RefreshExcel_FileName"].Value.ToString();

            PivotRefresh(path + filename);
            Dts.TaskResult = (int)ScriptResults.Success;
}
        
        private void PivotRefresh(string Filename)
        {
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();

            //oXL.Visible = true;
            oXL.DisplayAlerts = false;
            Microsoft.Office.Interop.Excel.Workbook mWorkBook = oXL.Workbooks.Open(Filename, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            //Get all the sheets in the workbook
            Microsoft.Office.Interop.Excel.Sheets mWorkSheets = mWorkBook.Worksheets;
            foreach (Microsoft.Office.Interop.Excel.Worksheet pivotSheet in mWorkSheets)
            {
                //Microsoft.Office.Interop.Excel.PivotTables pivotTables = pivotSheet.PivotTables();
                Microsoft.Office.Interop.Excel.PivotTables pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)pivotSheet.PivotTables();
                int pivotTablesCount = pivotTables.Count;
                if (pivotTablesCount > 0)
                {
                    for (int i = 1; i <= pivotTablesCount; i++)
                    {
                        pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
                    }
                }
            }
            mWorkBook.Save();
            mWorkBook.Close(false, Filename, null);
            oXL.Quit();
            mWorkBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
        }

Refresh the excel file using SSIS

C# code to refresh the excel report using SSIS Script task:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Microsoft.Office.Interop.Excel;

--------------------

public void Main()

        {

            // TODO: Add your code here

            ExcelRefresh(@"E:\TEST.xls");

            Dts.TaskResult = (int)ScriptResults.Success;

        }

     

        private void ExcelRefresh(string Filename)

        {

            object NullValue = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            excelApp.DisplayAlerts = false;

            Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(

               Filename, NullValue, NullValue, NullValue, NullValue,

               NullValue, NullValue, NullValue, NullValue, NullValue,

               NullValue, NullValue, NullValue, NullValue, NullValue);

            Workbook.RefreshAll();

            System.Threading.Thread.Sleep(20000);

                     

            Workbook.Save();

            Workbook.Close(false, Filename, null);

            excelApp.Quit();

            Workbook = null;

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

        }

-------------

Incase if you get any error like 'Cannot be embbedded. Use the applicable interface instead [duplicate]' then 

follow the below step:

Go to References - find the Microsoft Office Interop reference - Right click - select properties 

- change 'Embed Intero Types' to false.

How to remove rows from csv file using script task

Below is the C# code to remove specified rows from CSV file using script task

Add following references:

using System.IO;

using System.Collections.Generic;

Code
------

string line;
            StreamReader sr = new StreamReader(@"E:\Test.csv");

            List<String> file = new List<string>();
            
            while (sr.Peek() >= 0)
            {
                line = sr.ReadLine();
                if (line != "")
                {
                    if (!line.StartsWith("#")) //line starts with #
                    {
                        if (!line.Contains("Summary"))   //line contains string as summary
                        {
                            file.Add(line);
                        }
                    }
                }
            }
            sr.Close();
          
            File.WriteAllLines(@"E:\Test1.csv", file);
            //File.WriteAllLines(@"E:\Test.csv", file);

How to remove rows from excel using script task

Below is the C# code to remove selected rows from Excel work sheet using script task in SSIS

Add the following references: 

using Microsoft.Office.Interop.Excel;
using Microsoft.CSharp;

using System.Diagnostics;

Code
-------

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            // open the concrete file:
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excel.Workbooks.Open(@"E:\RemoveRows.xlsx");
            // select worksheet. NOT zero-based!!:
            Microsoft.Office.Interop.Excel._Worksheet excelWorkbookWorksheet = excelWorkbook.Sheets[1];
            // create a range:
            Microsoft.Office.Interop.Excel.Range range = excelWorkbookWorksheet.get_Range("A1:A2");
            Microsoft.Office.Interop.Excel.Range range2 = excelWorkbookWorksheet.get_Range("A4");
            Microsoft.Office.Interop.Excel.Range entireRow = range.EntireRow;
            Microsoft.Office.Interop.Excel.Range entireRow2 = range2.EntireRow;

            // iterate range
            
            entireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
            entireRow2.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
           

            // save changes (!!):
            excelWorkbook.Save();

            excel.Quit();

Dts.TaskResult = (int)ScriptResults.Success;