SSIS & Secure Sharepoint Connections

Question

SSIS & Secure Sharepoint Connections

Dirt McStain

SSCommitted

Points: 1582
More actions
June 2, 2010 at 5:49 pm

#387072

Specs:
Windows Server 2003 R2 x64 SP2
SQL Server 2008 10.0.2531 (64 bit)
Visual Studio 2008 v9.0.30729.1 SP
.NET Framework v 3.5 SP1
I'm trying to use SSIS to upload to a local (but authenticated) Sharepoint site and I'm running into some problems. I'm using a File System Task to copy a local file to a Sharepoint directory. The Sharepoint has a network directory that I point to (i.e. http://mysharepoint/documents/testing is the equivalent of \\mysharepoint\documents\testing in Explorer). The issue here is that this is an authenticated directory, so I get an error when trying to connect to it.
Description: The file name "\\mysharepoint\documents\testing" specified in the connection was not valid.
I've attempted a workaround by running an Execute Process Task before the File System Task that uses the following argument:
Net Use \\mysharepoint\documents\testing password /User:myusername /PERSISTENT:Yes
This doesn't seem to do anything. What can I use to accomplish this? Ideally, I would like to avoid using VB or C# scripts to accomplish this, but I'm open to ideas.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dinogas2000-sqlservercentral SSC Enthusiast Points: 104 More actions · Answer 1

There is a DLL that can be downloaded separately called SharePointUtility. It is a .Net Component you can put in the GAC and use to upload files to sharepoint sites (authenticated or not). Try it out and see if that helps.

Kevin

Dirt McStain SSCommitted Points: 1582 More actions · Answer 2

I don't think that this is applicable. The Sharepoint Connections for SSIS (which I have), are meant for Sharepoint List data transfers. From what I've seen, this is more like meta-data on the Sharepoint, not a method for transferring files to/from Sharepoint directories.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog

Dirt McStain SSCommitted Points: 1582 More actions · Answer 3

It turns out that the server had the WebClient service disabled, so it wasn't recognizing the unc path regardless of the fact that I was authenticated to it.

In short, uploading to a local authenticated SharePoint is actually very easy granted you have the following:

1) A unc path structure to the SharePoint destination

2) Have a user authenticated to the SharePoint

3) Set up a an account in Management Studio under Security\Credentials with the identity set to the authenticated user account

4) Set up a Proxy account under SQL Server Agent\Proxies that uses the credentials set up in step 3 and allow it to activate the following subsystems -- SSIS Package Execution, and Operating System (although ActiveX may be useful as well)

5) Set your jobs' SSIS step(s) to use the Proxy account instead of the generic SQL Server Agent Service Account

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog

dinogas2000-sqlservercentral SSC Enthusiast Points: 104 More actions · Answer 4

Not exactly, the one for SSIS, yes, but there is a DLL it uses called SharePointUtility which has additional functionality such as uploading, deleting, and creating folders on a sharepoint folder location (original use before it was extended for SSIS).

The method in there does not use webdav and is more forgiving for invalid ports and such.