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.