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);
        }

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;

To convert from JSON to XML and write

Sometimes, while we retrieve data from web API, the output comes in JSON format which we cannot use as a source in our SSIS ETL. So we need to convert it in to XML. Below is the code to convert.

Here the API output is stored in 's2'. Also we need add some the namespace along with adding them in reference.

using System.Xml;
using System.Xml.Linq;
using Newtonsoft.Json.Bson;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Schema;
using Newtonsoft.Json.Serialization;
using Newtonsoft.Json.Utilities;
using System.Runtime.Serialization.Json;

using Newtonsoft.Json;

public void Main()
{

XmlDocument test1 = JsonToXML(s2);
System.IO.File.WriteAllText(@"D:/Test/Test_Data.xml", test1.InnerXml);

Dts.TaskResult = (int)ScriptResults.Success;

}

public XmlDocument JsonToXML(string json)
        {
            XmlDocument doc = new XmlDocument();

            using (var reader = JsonReaderWriterFactory.CreateJsonReader(Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max))
            {
                XElement xml = XElement.Load(reader);
                doc.LoadXml(xml.ToString());
            }

            return doc;
        }

Writting the output file into the text file

To write the output of the retrieved web data in to the text format, use the below code:

Here the output is 'result'

System.IO.File.WriteAllText(@"D:\MobileApp_Distimo\MobileApp_Downloads.txt", result);

How to call the variable value in message box in script task editor

Here is an example of calling the variable value in the message box to display

MessageBox.Show(Dts.Variables["User::MaxHistoryRecordDate"].Value.ToString());

OR

DateTime Date = (DateTime)Dts.Variables["User::MaxHistoryRecordDate"].Value;

MessageBox.Show(Date.ToString());

How to call the assigned variables in script task editor

Below is the example of calling declared variables in script task editor. 

string userName = (string)Dts.Variables["User::UserName"].Value;

string password = (string)Dts.Variables["User::Password"].Value; 

Sometimes when we call the datetime variable, we need to convert it to the required format like 'dd-MM-yyyy' or with time, etc. Below is the example:

DateTime FromDate = (DateTime)Dts.Variables["User::FromDate"].Value;
string s = FromDate.ToString("yyyy-MM-dd");
DateTime ToDate = (DateTime)Dts.Variables["User::ToDate"].Value;

string s1 = ToDate.ToString("yyyy-MM-dd");

C# code to calculate check digit using DAMM Algorithm

C# code to calculate Damm Algorithm  -- In Script transformation task (in DFT)


public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string value = Row.sourcecodeid.ToString();
        Int32 intValue;
      
        if (Int32.TryParse(value, out intValue))
        {
            // mystring is an integer

            int column = 0;
           
            int[] intArray = new int[value.Length];
            for (int i = 0; i < value.Length; i++)
            {
                intArray[i] = int.Parse(value[i].ToString());
            }
            for (int i = 0; i < intArray.Length; i++)
            {

                int row = intArray[i];
                int newvalue = ReturnArrayValue(row, column);
                column = newvalue;
                if (i == intArray.Length - 1)
                    Row.checkdigit = column.ToString();
            }

        }
        else
        {
            Row.checkdigit = value;
        }
    }
    public int ReturnArrayValue(int column, int row)
    {
        int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 }, 
            {1,7,5,0,9,8,3,4,2,6},
            {6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
            {9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
        return myArray[row, column];
    }
   
}



C# code to calculate Damm Algorithm  -- In Script  task (in control flow)



public void Main()
{
// TODO: Add your code here


            string value = Dts.Variables["User::NumberSequence"].Value.ToString();
            //checkDigit(val);
            Int32 intValue;
   
            if (Int32.TryParse(value, out intValue))
            {
                // mystring is an integer

                int column = 0;
         
                int[] intArray = new int[value.Length];
                for (int i = 0; i < value.Length; i++)
                {
                    intArray[i] = int.Parse(value[i].ToString());
                }
                for (int i = 0; i < intArray.Length; i++)
                {

                    int row = intArray[i];
                    int newvalue = ReturnArrayValue(row, column);
                    column = newvalue;
                    if (i == intArray.Length - 1)
                        Dts.Variables["User::CheckDigitTest"].Value = column.ToString();
                    

                }

            }
            else
            {
                Dts.Variables["User::CheckDigitTest"].Value = value;
               
            }
  Dts.TaskResult = (int)ScriptResults.Success;
}

public int ReturnArrayValue(int column, int row)
        {
            int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 }, 
            {1,7,5,0,9,8,3,4,2,6},
            {6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
            {9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
            return myArray[row, column];
        }