I was working with loading data from list of excel to the SQL database and the package was using SSIS foreach loop container, Excel source and OLEDB destination.
while I was running the package Excel connection Manager was giving the error as like below screenshot (Fig 1)
Fig 1: Error found at excel connection manager
Problem started when I have changed source from specific file to the whole folder path from the expression window:
Fig 2: Expression window from excel connection manager property
Error message was like below:
"Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 "
I was looking into fix the issues, after googling it I found different things can happen with the same error code: 0x80004005, however; after trying different way I found the fix which is changing the
Run64BitRunTime property from TRUE to FALSE.
How to do?
At first. right click on the Project under solution explorer and open the property window which look like below:
Fig 3: Find out the property window
Now from the property window, go to the Configuaration Properties->Debugging and then change the value for Run64BitRunTime from TRUE to FALSE and then click OK to save the changes.
Fig 4. Update property value
It may still show error when you click Excel source and edit it, however if you run the package or loop container it will run and save the data to the database.