Import Excel file located in SharePoint Server into SSIS

1
votes

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.

2
excelvisual-studiosharepointssisssms
What is the error that you are getting? – rvphx
I added the full error messages I am getting. – Michelle Turner
Do you have permissions to create a file at the said location? Can you right click on the location and simply create any file? – rvphx
Thank you for your comment. I have no permissions to create a file at the location. I added this information in the question. – Michelle Turner
If you dont have the rights to create a file, I suppose that is your real problem. Try getting the rights to create a file at the location and there should be no other issues. If not, I am afraid, there's not much this forum can help you with. – rvphx

2 Answers

1
votes

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.

  1. 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.

  2. 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.

0
votes

Try the following steps in your project file under Solution Explorer:

  1. Right-click on the project and select Properties,
  2. When the Properties page opens, select Debugging under Configuration Properties on the left,
  3. On the right under Debug Options, change the value for Run64bitRuntime from True to False,
  4. Select the Apply button.