using System;  
 using System.Data;  
 using Excel = Microsoft.Office.Interop.Excel;  
 using Microsoft.Office.Interop.Excel;  
 using System.IO;  
 public void Main()  
 {  
   try  
    {  
     string sourceExcelPathAndName = @"G:\B01445_20150401-20150430.xls.xlsx";  
     if (ConvertExcelToDataTable(sourceExcelPathAndName))  
    {  
     Dts.TaskResult = (int)ScriptResults.Success;  
    }  
     else  
    {  
     Dts.TaskResult = (int)ScriptResults.Failure;  
    }  
    }  
    catch (Exception ex)  
    {  
     Dts.TaskResult = (int)ScriptResults.Failure;  
    }  
  }  

 public static bool ConvertExcelToDataTable(string sourceExcelPathAndName)  
 {  
  try  
  {  
 System.Data.DataTable dt = new System.Data.DataTable();  
  DataRow dr;  
  int r;  
  int c;  
  int intRows;  
  int intCols;  
  int headerRowsToSkip = 0;  
  oXL = new Excel.Application();  
  oXL.Visible = false;  
  oXL.DisplayAlerts = false;  
  Excel.Workbooks workbooks = oXL.Workbooks;  
  mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);  
  //Get all the sheets in the workbook  
  mWorkSheets = mWorkBook.Worksheets;  
  //Get the specified sheet  
  mWSheet1 = (Worksheet)mWorkSheets[1];  
  Excel.Range range = mWSheet1.UsedRange;  
  //deleting the specified number of rows from the top  
  Excel.Range rngCurrentRow;  
  for (int i = 0; i < headerRowsToSkip; i++)  
 {  
 rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;  
  rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);  
 }  
  Object[,] values = (Object[,])range.Value2;  
 intRows = values.GetLength(0);  
  //Getting Data into DataTable  
  if (intRows != 0)  
 {  
 intCols = values.GetLength(1);  
  if (intCols != 0)  
 {  
  //Getting Columns Names  
  for (c = 1; c <= intCols; c++)  
 {  
  if (!String.IsNullOrEmpty(Convert.ToString(values[1, c])))  
 {  
 dt.Columns.Add(new DataColumn((String)values[1, c]));  
 }  
 }  
  //Getting Data and binding DataRow  
  for (r = 2; r <= intRows; r++)  
 {  
 dr = dt.NewRow();  
  for (c = 1; c <= dt.Columns.Count; c++)  
 {  
  if (!String.IsNullOrEmpty(Convert.ToString(values[r, c])))  
 {  
 dr[(String)values[1, c]] = Convert.ToString(values[r, c]);  
 }  
  else  
  {  
 dr[(String)values[1, c]] = String.Empty;  
 }  
 }  
 dt.Rows.Add(dr);  
 }  
 }  
 }  
  return true;  
 }