Products
Services
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: (2372)
Favorites:

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;

  try
  {
    Dts.VariableDispenser.LockForRead("User::FileName");
    Dts.VariableDispenser.LockForRead("User::Xml");
    Dts.VariableDispenser.LockForRead("User::MaxBatchId");

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

    //Get File
    if(!File.Exists(fileAddr))
    {
      throw new Exception("the directory specified does not contain an xlsx file");
    }
    else{
      fileEntries = new string[1];
      fileEntries[0] = fileAddr;
      
      if(fileEntries[0].IndexOf("~$") > -1){
        return;
      }
    }
    
    //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);
    dbConnection.Open();

    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");
    }else{
      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
    myCmd.Fill(objExcelDS);

    myCmd.Dispose();

    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()
      }
      theseRows.Add(thisSts);
    }

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

    vars.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
    dbConnection.Close();

  }
  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:
*Email:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]