0

Stored Procedure on SQL Server 2017 calls an OPENROWSET command to load a file from an .xlsx file.

When running the Stored Procedure on the server it works fine and will load the xlsx rows into data tables. However when the SSIS package runs the Execute T-SQL Statement task to EXEC the same stored procedure it gives me this error:

"[Execute SQL Task] Error: Executing the query "EXEC USP NAME" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The connection being used in the package has full access to the folder that contains the xlsx file and the Test Connection works just fine.

Hadi
  • 36,233
  • 13
  • 65
  • 124
mattjowen
  • 1
  • 2
    An obvious question would be why you're using a stored procedure, instead of the native options in SSIS for reading Excel files, which is probably much easier to get right. – Jeroen Mostert Jul 10 '19 at 12:26
  • 1
    @JeroenMostert When I add a connection to my xlsx using and Excel Source Microsoft Visual Studio shows me another error, "Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to the source using the connection manager '" – mattjowen Jul 10 '19 at 12:48
  • Is the Excel file actually readable from your machine? When designing and debugging the package, you should point the connection to a local file, then change it to the correct (server-side) location in the configuration when it's actually deployed. – Jeroen Mostert Jul 10 '19 at 12:50
  • @JeroenMostert Absolutely, the file is on the C:\ drive of the server that has SSIS and SQL Server installed. I can open and edit the file without any issue. – mattjowen Jul 10 '19 at 12:59
  • 3
    Does the SSIS package run on the same server as the database (and the Excel file)? Does the package run in the default 64 bit mode or 32 bit mode? What user is runs the SSIS package? – billinkc Jul 10 '19 at 14:06
  • If you run your OPENROWSET proc directly from SSMS while logged in as the same user as your connection manager is using, does the proc work? – digital.aaron Jul 10 '19 at 14:58

0 Answers0