Use of Script task for converting text into number format of excel in SSIS package

You are using the OLE DB Source and Excel Destination task in the Data Flow tab for getting the Table or SQL data in the Excel sheet but the number column is converted into text format in Excel destination. We need that column in number format for calculations

Use the following script in SCRIPT TASK in the Control flow tab for manipulating the excel column to Number format from text format:

Suppose I want to convert column 15 from excel sheet 1 to number format. Drag the Script task and use the following script to convert it into number format:

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(@"D:\DevelopmentFolders\WhoIsActive_Temp.xlsx");
             

//use for loop for mulitple sheets		 
       //  for (int i = 1; i < 4; i++)
       // {
       
//Use for first sheet use 1 in worksheets[1]             
       Excel.Worksheet worksheet = workbook.Worksheets[1];
				
//use for loop for mulitple column index for your need
               //   for (int ColumnIndex = 1; ColumnIndex < 22; ColumnIndex++)
               //   {
         
//I changed only for columns 15 to numberformat
               worksheet.Columns[15].TextToColumns();
               worksheet.Columns[15].NumberFormat = "0";
//if worksheet having decimal number format then use as:
              //  worksheet.Columns[15].NumberFormat = "0.00";
      
              //   }
       // }
            
         workbook.Save();
         workbook.Close();
	 app.Application.Quit();
         Dts.TaskResult = (int)ScriptResults.Success;
     }