"Unspecified Error" when clicking on 'Name of Excel Sheet' for a Excel Source Task

I've created an Excel connection. When I create a data flow with an Excel destination and click 'Name of Excel Sheet' I get an "Unspecified Error". When I look under 'Show Advanced Editor' of the destination I see the following error:

Error at blah [Connection manager "DestinationConnectionExcel"]: 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: "Unspecified error". Error at Insert blah [Destination - blah [199]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection开发者_如何学Python method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

I've read through a tons of other post and nothing seems to help. I've tried setting the 'DelayValidation=True' on all the Jet Engine related task. While this allows me to run the package without any errors, I can't edit or change anything. I also can't create new tasks with Excel.

I've already tried to set 'Run64BitRuntime = false" and that also isn't helping.

Another note, The problem is intermittent. I've been able to work with Excel tasks fine one time after a reboot, then another time its all failing as I described.

Any help would be appreciated. Thanks all

I encountered a similar issue, and the resolution was two-fold for me. First, make sure certain dlls are properly registered on your dev machine:

Paraphrased from SSIS 2005: Using Execute SQL Task to work with Excel Connection Manager

Please check whether the following files exist:

C:\windows\system32\odbcjt32.dll

C:\windows\system32\msjet40.dll

C:\windows\system32\msexcl40.dll

C:\Program Files\Common Files\System\Ole DB\oledb32.dll

C:\Program Files\Common Files\System\ado\msado15.dll

If they are, please manually register them. Run each of the following commands from command prompt:

Regsvr32 "C:\Program Files\Common Files\system\Ole DB\oledb32.dll"

Regsvr32 "C:\Program Files\Common Files\system\ado\msado15.dll"

Regsvr32 "C:\windows\system32\msjet40.dll"

Regsvr32 "C:\windows\system32\msexcl40.dll"

Then, update any folder/file paths referenced in the SSIS package. I kept changing the Excel Connection Manager object to point to the correct file, but the change would never "stick." Another developer directed me to look at the list of Variables in the SSIS package. Right-click anywhere in the package designer and choose "Variables" (based on the 2008 version). In the list of variables, check if there are any paths that are being used. If you find any, verify that they exist on your machine or change them to use your own local paths.

After following these two suggestions, I was able to open the Excel file and choose the target sheet in the Excel Connection Manager window. And, the SSIS package runs successfully in my local environment.

I take it the path to the excel workbook is correct? I also had problems when I had opened and saved the excel workbook I was using as my destination in excel. I think if you create the workbook from within SSIS it might use office web tools. Perhaps you could try to recreate the workbook within SSIS.

I got similar problem,make sure connection manager is connected to MicrosoftExcel-64 bit which is same as SQL SSIS version. Otherwise add Microsoft.ACE.OLEDB.12.0 provider in your linked server, not in your local machine.

And also check if you have Microsoft excel present in your server again not on your local machine

If local machine and SQL Server installed on the same machine then its fine.