Use SSIS to read files in SharePoint library

Question

Hi,
I'm trying to use SSIS to read data from an Excel file in a SharePoint document library and I am having some problems with it.

First I tried to use the Excel file as a source file in an Excel Source task which worked well in BIDS but not on the SQL server. ("CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER")

Next I tried to read data from an Excel file on the SQL server file system which worked well on the SQL server.

Next I tried to use a File System Task to copy the Excel file to the SQL server file system. This also worked well in BIDS but not on the SQL server. ("The file name \\sharepoint\mylibrary\myfile.xls specified in the connection was not valid.")

I have also tried to let the SQL server agent run on a domain account that is set up as a reader on the SharePoint document library without success.

Where am I going wrong?

Answer 1

It appears that your server setup doesn't have access to the Sharepoint library.

Unfortunately, you're not real clear on some pertinent details, so please answer these questions to provide more information:

1. Can you log in to the server and execute your package (the one that reads the Excel File using an Excel Source) from the command line with DTExec or DTExecUI?
2. Is your server a 32-bit or 64-bit OS?  Is the SQL installation 32-bit or 64-bit?
3. Restore the default account for SQLAgent.  Create a Credential and Proxy for your account in SQL Server.  Configure your SQLAgent job to use that proxy to execute your package.

Let me know the results of those...

---

Answer 2

Hi Todd, thanks a lot for taking the time to reply. Sorry I haven't replied with more info before. Been away on Christmas holidays.

1. When I run the package from command line with DTExec I get the same error as when I run it in SSMS.
   (I have a similar package with the only difference that it reads from an Excel file located on the SQL server file system rather than on SharePoint. That one works in SSMS but not with DTExec.) Maybe I'm doing something wrong? I execute "dtexec /sq ExcelTest". (I'm a newbie when it comes to server matters, sorry.)
2. The server is a 64-bit Windows Server 2003 and the SQL installation is 64-bit as well.
3. The SQL Server Agent is restored to run on the Built-in account Local System. I have created a credential for a user that has access to the files (both the one on the server and the one on SharePoint) and then created a proxy that uses this credential. Then I created two jobs that executes different packages. Both using the same proxy and both with "Use 32-bit runtime" checked. The one that reads from an Excel file on the server file system works but not the one that reads from SharePoint.

Can you make something out of this info?

/Marcus

Answer 3

If you navigate to the library where the file is stored, right-click on the link and "copy shortcut".  Then, in the connection manager where it requests the path to the file name, press Browse.  Paste in the contents of the clipboard, then remove the filename part of the text - just leave the path.  Press "Open", and the browse window will show you the contents of the library.  Pick the right file.  You'll see the new path is slightly different, but it will work...

---

Answer 4

That was (more or less) the way I did it before. So this gives me the same path and I get the same error message.
As I said, it does work when I execute the package from BIDS on my workstation, but not in SSMS on the server.

However, I have now narrowed down the problem. If I paste the UNC path to the folder in a file explorer window on my workstation it works, but if I do the same on the server it doesn't. Turns out Windows 2003 server can't display webfolders. If I browse to the sharepoint document library in internet explorer on the server and select Actions >> Open with Windows Explorer, nothing happens.

I found this post, which seemed promising, but since I'm on a 64-bit server the update didn't solve my problem.
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sharepoint.general&tid=0fa9e19e-68bd-4d10-a0b7-e5867e0fc1b3&p=1

So I guess there is little hope for reading data from an Excel file in a SharePoint library using SSIS on a 64-bit Windows 2003 server?

Answer 5

When I did the steps above, I got two different paths.  The original "copied" shortcut:

http://sharepoint/sites/MySite/Shared%20Documents/Document%20Name.xls

And the "altered" version, after my steps:

\\sharepoint\DavWWWRoot\sites\MySite\Shared Documents\Document Name.xls

Perhaps you could manually adjust your path to something like the above (use \\servername\DavWWWRoot\restofpath?)

---

Answer 6

OK, sorry, I was a bit unclear in my intitial post. I have the \\sharpoint\DavWWWRoot\mysite\mylibrary\myfile.xls path. I simplified it too much in my post. It is his path I can't open in a windows explorer on the server but I can on my workstation. (See my previous post.)

Answer 7

Is your "server" the SharePoint server?

Have you tried going to the server, pressing the "start" button, then "run", then pasting in the UNC of the XLS file to verify it's correct?  If that works with your login, then I can only assume that it's now a permissions issue that the job you're running doesn't have permission to read the file from SharePoint.

---

Answer 8

I was playing around with the same issue. In my case SSIS and SharePoint run on the same server.

In the IIS Log I noted that requests to the SharePoint library are made without credentials. This returns HTTP 404.

I enabled anonymous access to the SharePoint Site and this Library without any change.

IIS Log:

date

time

s-ip

cs-method

cs-uri-stem

cs-uri-query

s-port

cs-username

c-ip

cs(User-Agent)

sc-status

sc-substatus

sc-win32-status

time-taken

07.01.2010

11:19:31

(removed)

PROPFIND

/SiteName/Test_Library

-

80

-

(removed)

Microsoft-WebDAV-MiniRedir/6.0.6002

401

5

0

11

Using the solution by Jason solved this: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/aea4a334-bc13-4fd2-aa59-6d31dfcc45e2

In my SSIS Control Flow I run a Execute Process Task calling Net.exe with Params "Use \\share\folder password /User:domain\user /PERSISTENT:Yes" command before the file task. On the file connection I set DelayValidation to true.

Answer 9

No, the "server" is my SQL server. Thanks for your effort, but I don't think it has to do with permissions. I think it has to do with the related issue I posted before: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sharepoint.general&tid=0fa9e19e-68bd-4d10-a0b7-e5867e0fc1b3&p=1
Because on my SQL server, I can access my SharePoint document library in Internet Explorer but nothing happens when I select Actions >> Open with Windows Explorer. And I can't open a Windows Explorer window and paste in the link \\sharpoint\DavWWWRoot\mysite\mylibrary\myfile.xls (which works on my workstation logged in as the same user).

Answer 10

I think you should post this issue - basically your last statement there - in the SharePoint ECM forum for some help.  (Please do return here and post the solution when you get one!)

---

Answer 11

I was reading your post while searching for an answer to an issue I'm researching.  I have a DBA that is attempting to pull contents from an Excel file in a SharePoint library.  He's able to run the package from his workstation but not the SQL server.  The SQL instance is on it's own server, not part of the SharePoint farm.  He receives the message below:

microsoft office access database engine cannot open or write to the file. it is already opened exclusively by another user,or you need permission to view and write its data

Anyhow, if you are still unable to access the library via UNC path, enable the Webclient service in the services console. 

You'll be able to access the library afterwards.

Answer 12

has anyone had any luck with this. I am trying to access Sharepoint via a UNC path. 

Running SSIS 2008 R2 on server 2008 R2 (64 bit)

I am using the ACE OLE provider 12.0 the job is running in 32 bit mode.

I can access the Sharepoint doc lib with a UNC path from the server but only after I provide my credentials.

The proxy account has plenty of access to both sharepoint and the SSIS server. CANNOT AQUIRE CONNECTION is the error.

I have enabled desktop experience I am using the UNC path with  davWWWroot (\\myportal.com\davWWWroot\mysite\Doc Lib\MyFile.xlsx)

Any help would be appreciated.

Answer 13

I downloaded SSIS SharePoint data source and destination executable from Microsoft  http://msdn.microsoft.com/en-us/library/dd365137(v=SQL.100).aspx

 Our Sharepoint farm is using a trusted security configuration, so I needed to supply credentials in a connection manager.  Right click in Connection Managers, choose New Connection and choose SPCRED as the type.

 

I was able to connect to lists and document library views. 

 

Note that SharePoint views have some columns defined multiple times (like the Title is also a link to edit the document, so that column is listed twice in the same view).  You just need to change the second occurance to be ignored when mapping and remove it from the external and destination column lists in the SharePoint Source object.

Answer 14

HI JSJatFIS,

I have given my link http://sharepoint.xyz.com/sites/abc/Shared Documents/abc.xls in SiteURL - component properties tab - Advanced Editor for Sharepoint source.

It is asking list name. What should I do? How do I find the list name for this? Please help me.

Answer 15

Hi emcus,

did you succeed read data from an Excel File in a Sharepoint library? Did you use web services?

I want to read\write sheets from Sharepoint to a SQL db and back.

I'm looking if it is possible in SSIS without code by using the web service copy
(https://sharepointsite/sites/subsite/_vti_bin/Copy.asmx)

Anton

Answer 16

kdinuk,

In your example, "Shared Documents" is a document library and that is what you would put in the List Name field

Answer 17

Has anyone had success writing to the UNC path from ssis to sharepoint using web services/  "web client enabled" and Desktop experience installed?  I am having the same problem and this is the only solution that i have found that might work....  I am worried about the below quote.....

http://mossdefinitely.blogspot.com/2010/08/enabling-webdav-in-sharepoint-windows.html

"Set your client's expectations - the performance of folders accessed through SharePoint WebDAV is poor compared to native Windows folders, expecially if you are using a https protocol."