Contact Us

Scripts: C# :: Ssis packages :: Library Article #17

Developer's Section

Read an Excel File inside an SSIS Script Task
By: Erobo Software

Hire a Developer for Related Work / Installation | $55 hr
Rating:  | Rate It:   
Average Votes: (1975)

Learn to use SSIS and C# to load your Excel files into a variable for further processing.

Use the code snippet below to install a script that can load an excel file into an array that you can use to process data:

 Code Snippet 1

public void Main()
  Variables vars = null;
  string[] fileEntries = null;
  string[] eventTypes = null;


    string fileAddr = vars["User::FileName"].Value.ToString();
    vars["User::Xml"].Value = "";
    int BacthId = Int16.Parse(vars["User::MaxBatchId"].Value.ToString());

    //Get File
      throw new Exception("the directory specified does not contain an xlsx file");
      fileEntries = new string[1];
      fileEntries[0] = fileAddr;
      if(fileEntries[0].IndexOf("~$") > -1){
    //Connect to an Excel File
    string m_strConnection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0} ; Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;ImportedMixedTypes=Text;\"",fileEntries[0]);

    string firstSheetName = "";

    OleDbConnection dbConnection = new OleDbConnection(m_strConnection);

    System.Data.DataTable dbSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

    if(dbSchema = null || dbSchema.Rows.Count < 1){
      throw new Exception("Error: Could not determine the name of the first sheet worksheet");
      firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

    DataSet objExcelDS = null;

    //Query spreadsheet to extract all data
    OleDbDataAdapter myCmd = new OleDbDataAdapter("Select * FROM [" + firstSheetName +"]",m_strConnection);

    //Initialize DataSet
    objExcelDS  = new DataSet();

    //Fill Dataset


    List<string[]> theseRows = new List<string[]>();

    int RowCount = 0;

    foreach(DataRow row in objExcelDS.Tables[0].Rows){
      string[] thisStr = new string[row.ItemArray.Length];
      for(int j = 0; j < row.ItemArray.Length; j++){
        thisStr[j] = row.ItemArray[j].ToString().Trim()

    vars["User::MaxBatchId"].Value = (BatchId + 1);


    Dts.TaskResult = (int)ScriptResults.Success;

  catch(Exception e){
    Dts.TaskResult = (int)ScriptResults.Failure;
    Dts.Events.FireError(-1,"","InnerException:" + e.Message,"",0);


See other Scripts in Ssis packages

Submit Your Scripts:

System Message:
  • Your C# script has been sent. Please allow 48 hours for processing.

If you would like to have your Javascripts published in this section please fill out
the form below:
*Your Name or Username:
Home Town:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]