Import Excel file located in SharePoint Server into SSIS

1

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.

excel
visual-studio
sharepoint
ssis
ssms
asked on Stack Overflow Jun 15, 2018 by Michelle Turner • edited Jun 25, 2018 by Michelle Turner

2 Answers

1

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.

answered on Stack Overflow Mar 15, 2020 by Ian • edited Mar 15, 2020 by Dharman
0

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.
answered on Stack Overflow Jun 18, 2018 by user3662215 • edited Mar 15, 2020 by Dharman

User contributions licensed under CC BY-SA 3.0