Import Excel file located in SharePoint Server into SSIS

In an SSIS Data Flow Task I seek to import data using an Excel Source Task connected to an xlsx file located in a SharePoint server, preferably without downloading additional software.

In the Excel Connection Manager I use the following Excel file path format (UNC):

\\sps.servername.com\projects\new\Shared%20Documents\excelfilename.xlsx

I also tried using a space character instead of '%20', and tried out all available Excel versions in the Connection Manager.

I receive the following error messages:

Validation error. Package Connection manager "Excel Connection Manager": 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 Description: "Failure creating file.".

Validation error. Data Flow Task: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Please note the following:

I have reading rights in the location of the file, but no permissions to create files.

As a workaround it would help to find a way to import the data into a sql server using a stored procedure (or view).

Unfortunately, it is no option to move the excel file from the SharePoint server.

I have had the same issue as you for a while and I needed a quick and easy way to access excel files on a sharepoint site without the use of a piece of complicated code. I have figured out a very easy way to do this.

1. Go to the location of your excel spreadsheet in sharepoint.

2. On the ribbon at the top of your list of files use the "All Documents" dropdown list and select "View in File Explorer".

3.This will take you to your file explorer and you will see the directory name that mirrors the folder in sharepoint.

4. At the top left hand corner of file explorer you will see the "Pin to Quick access" icon. Click the icon. This will give you constant access to your on SharePoint.

5. Go in to your SSIS package and use the Excel Source. You should be able to access the file from there.

Note: I am using SharePoint 2013. Depending on what SharePoint version you have will depend on how you link your SharePoint files to file explorer. You should be able to find a video on how to do that. The key here is having access the your files in file explorer.