0

Context

I'm trying to create an SSIS package to import from EXCEL (Microsoft Excel Worksheet (.xlsx)) files into SQL Server 2012. All files have a single tab with the same tab name.

  • The process works when I specify a single file.

  • The process fails when I try to use a For Each container.

In the For Each File Enumerator I've created a variable [user@ExcelFileName]

Error

In the expressions of the excel connection manager, I've attempted the following values for the ConnectionString:

> -@[User::ExcelFileName]

-"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFileName] + ";Extended Properties=Excel 8.0"

-"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Filename] + ";Extended Properties=\"Excel 12.0\";"

  • "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Filename] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

They all fail.

This is the error message:

Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" 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 Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at Package [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E73. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E73 Description: "Format of the initialization string does not conform to the OLE DB specification.".

Any suggestions ?

Ben
  • 485
  • 9
  • 19
  • Do you have the Excel files open while running SSIS? I often see this error when I forget to close. – David Rushton Dec 07 '15 at 15:46
  • No, I do not - the files are closed. – Ben Dec 07 '15 at 16:15
  • Can you get an Excel connection to work manually? Once you can do that you can use your variable @[User::ExcelFileName] to override the connections ServerName property. Excel can be hard to work with in SSIS. I've never managed to get an xlsb working, only xls and xlsx. Also it's worth checking if the package is running in 32/64 bit mode (Project >> Properties Debugging >> Run64BitRuntime should equal FALSE). – David Rushton Dec 07 '15 at 17:14
  • Please see this tutorial. It's quite comprehensive. http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package –  Dec 09 '15 at 16:38
  • I ended up bulk converting the files into CSV and importing them that way. Thank you for the tutorial link. I'll try that next time. – Ben Dec 14 '15 at 19:24
  • this problem maybe because file excel using excel version 64bit, its more easy using csv file – kucluk Apr 08 '22 at 06:45

1 Answers1

0

You need to run your package in 32bit environment so set Run64BitRuntime property of the project to false. After that start your package.

AdrianChodkowski
  • 114
  • 2