How to pass different excel files to Excel connection manager in SSIS?

Question

I am passing different excel files to SSIS package. I am picking up from folder by using for each loop, mapped variable in that. I am passing that variable to Excel connection manager, but it is not taking. We are getting below error, but if we hardcode the excel file path it is working fine, but I want it should take from for each loop, because we are getting different Excel files, for example: abc.xls or abc(1).xls or abc 03-03-2016. How to handle this in SSIS?

Error:
TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Load Attendance data [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Load Attendance data [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error at Load Attendance data [SSIS.Pipeline]: One or more component failed validation.

Error at Load Attendance data: There were errors during task validation.

Error at Tardy Load [Connection manager "Excel Connection Manager 2"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Invalid argument.".

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Answer 1

This is the approach we took to handle a similar problem:

• The For Loop assigns the full path of the Excel file to a variable
• In the Excel Connection Manager, set the ExcelFilePath element of the Expressions property to that variable

• Inside the loop, we have a Script task that will extract the first sheet name assigning it to a ReadWriteVariable in the Script task:

  public void Main()
  {
      string ConnectionString;
      OleDbConnection ExcelConnection;
      DataTable TablesInFile;
      string FirstSheet = "";
  
      ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Dts.Variables["WorkingFolderAndFile"].Value.ToString() + ";Extended Properties=Excel 8.0";
      ExcelConnection = new OleDbConnection(ConnectionString);
      ExcelConnection.Open();
      TablesInFile = ExcelConnection.GetSchema("Tables");
  
      foreach (DataRow SheetInFile in TablesInFile.Rows)
      {
          FirstSheet = SheetInFile["TABLE_NAME"].ToString();
          break;
      }
      Dts.Variables["WorkingSheetName"].Value = FirstSheet;
  
      ExcelConnection.Close();
      ExcelConnection.Dispose();
  
      Dts.TaskResult = (int)ScriptResults.Success;
  }
  

• Then in the Excel Source Editor, for Data Access Mode, select: Table name or view name variable. And for the variable name, use the variable that contains the first sheet name that was assigned in the Script task.

• Also for the Excel Source, you may also have to set these properties:
• ValidateExternalMetadata = False
• AccessMode = OpenRowset From Variable
• OpenRowsetVariable = the sheet variable

Answer 2

Excel doesn't have the capability to regenerate every time, The same package with CSV will work fine.

But, iF you want to use the excel file for sure then Use an Execute SQL task and use an excel connection manager and add the script.

Example:

CREATE TABLE
`Excel Destination` (
    `PromotionKey` INTEGER,
    `PromotionAlternateKey` INTEGER,
    `EnglishPromotionName` NVARCHAR(255),
    `SpanishPromotionName` NVARCHAR(255),
    `FrenchPromotionName` NVARCHAR(255),
    `DiscountPct` DOUBLE PRECISION,
    `EnglishPromotionType` NVARCHAR(50),
    `SpanishPromotionType` NVARCHAR(50),
    `FrenchPromotionType` NVARCHAR(50),
    `EnglishPromotionCategory` NVARCHAR(50),
    `SpanishPromotionCategory` NVARCHAR(50),
    `FrenchPromotionCategory` NVARCHAR(50),
    `StartDate` DATETIME,
    `EndDate` DATETIME,
    `MinQty` INTEGER,
    `MaxQty` INTEGER
)

The query will generate a new excel file everytime you run the package and will solve your issue.

Hope it solves your issue.

Answer 3

Create a variable Excel File source as String and add the location of excel folder , create another variable excel file Name as a string and also add one file name to it . Go back to your connection manager , on one of your excel file ...right click to to properties , under expressions select Excel file Path then @[$Package::ExcelFileSource]+"\"+ @[$Package::ExcelFileName]. This will let you not hard code your excel file names .