Share via

Setting up prerequisities for SSIS package to retrieve data from an excel workbook hosted on SharePoint Site or any WebDAV Share

  • Article

Scenario

Let's assume you run into a requirement for extracting data from an Excel workbook hosted on a SharePoint URL using an SSIS package. It may be a bit tricky to implement if you aren't aware of the prerequisites needed to get this package running successfully.

Background

When an excel workbook or any document is hosted on SharePoint, it is accessible through a WebDav File share URL which makes it accessible over file system like any normal share. Now, you can use this file share as the source of your SSIS package. In my case the webdav share looks like \\sampleapplication\DavWWWRoot\sites\Documents\test.xls.

If you haven't setup the prerequisites, you may notice some of the known behaviours as described below :

  1. The SSIS package fails on execution from SQL Server Data tools, but starts running successfully after accessing the excel file from filesystem using the share location(after entering the login prompt details)
  2. After deploying the SSIS package to the SSISDB catalog, you may run into some of these errors as noted below :

Error 1 :

Description: 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: "The Microsoft Jet database engine could not find the object '\\sampleapplication\DavWWWRoot\sites\Documents\test.xls'.  Make sure the object exists and that you spell its name and the path name correctly.".

Error 2 :

Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "Failure creating file.".

Steps to Follow

For a successful setup of SSIS package for this scenario, You can start creating the SSIS package by following the general set of prerequisites that you would need for extracting data from any excel file :

  1. Install ACE OLEDB provider(either 32 bit or 64 bit) on the machine where your SSIS package will run
  2. Create a data flow task and use the Excel Source task for accessing the excel file
  3. Setup the SSIS package to run on 32 bit run time/ 64 bit run time, depending on the ACE OLEDB provider that you have installed

Apart from the above prerequisites, you need to follow the below steps separately with respect to accessing the Excel from SharePoint/any other WebDAV share :

  1. Install Web Client Service on the machine where SSIS package will run. This step is a prerequisite for you to be able to access documents over WebDAV share. In case you aren't able to access the share using file system, then you probably don't have this service installed. To verify this, you can navigate to Windows Services by entering "services.msc" in the Windows Run prompt and clicking enter. Look for the below service:

webclient

           In case you aren't able to find this service, please refer the steps in this link to enable desktop experience which will thereby install this service on a Windows server machine

         2. When you access the share via file system, you generally get a login prompt to authenticate your   access to the share. For the SSIS package to be able to handle this prompt successfully without any access denied errors, add a registry key AuthForwardServerList  with type as "multi string value" in the location HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters. The value of this key for my particular example will be the sharepoint url :  https://sampleapplication.  For more information, you can refer the section Registry Information in the kb article.

I hope this information helps you to avoid running into the problems that i did and enables you to quickly setup the SSIS package for such scenarios. In case you do have any questions, please drop a comment below and I will be glad to respond.