Reading Particular cell from a excel file using Script task in SSIS

We are getting the excel file and our requirement is like we need to read the value of the particular cell value. This cell is fixed from where we need to read this value.

In this deme we will see how to read this value using Script task.

Below is the example file.  

In this file, we need to read the value of Cell H4 . In this the cell we are getting the sum of Sales Amount column.

Taking script task 

We need to write the below code in the script task.

public void Main()                                 {                                                 // TODO: Add your code here             try             {                 string FolderPath = Dts.Variables["User::Source_Dir"].Value.ToString();                 string File_Name = Dts.Variables["User::File_Name"].Value.ToString();                   //Declare and initilize variables                 string filePath = "";                 filePath = FolderPath + "\\" + File_Name;                 string tabName = "Sheet1$";                 String strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="                 + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";";                 OleDbConnection cn = new OleDbConnection(strCn);                 string strSQL = "Select * From [" + tabName + "H4:H4]";                 OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, cn);                 DataSet ds = new DataSet();                 objAdapter.Fill(ds, "dSheet1");                 DataTable dt = ds.Tables["dSheet1"];                 foreach (DataRow row in dt.Rows)                 {                     MessageBox.Show(row[0].ToString());                   }                 cn.Close();                 Dts.TaskResult = (int)ScriptResults.Success;             }             catch (Exception ex)             {                 using (StreamWriter sw = File.CreateText(Dts.Variables["User:: Source_Dir"].Value.ToString()                         + "\\" + "ErrorLog.log"))                 {                     sw.WriteLine(ex.Message.ToString());                     Dts.TaskResult = (int)ScriptResults.Failure;                 }              }        }

Now we are ready to run this script.

 

Get the expected result.