0

I have an SSIS package connection that connects to a Sharepoint site w/ a UNC path, like so:

\\[Site URL]\DavWWWRoot\sites\DTS_BURM\DARBenchmarks\FileName.csv

My package reads the file and imports it into the SQL DB. This process works fine in VS. However, if I want to run the package from the SQL Server Agent in a job, it will provide me with the following error:

Import RAW Data:Error: Cannot open the datafile "[Site URL]\DavWWWRoot\sites\DTS_BURM\DARBenchmarks\FileName.csv".

Initially this seemed very obvious to me. The account that runs the SQL jobs does not have access to the SharePoint. So I added a Service Account to the Credentials folder, and then used those credentials to create a proxy for the SQL Server Agent. Also, I added that service account to the SharePoint as an owner so it would have read/write permissions.

Even after all of that, I still received an error stating it "Cannot open the datafile". I'm not sure what else I can do, if anyone has suggestions, it would be much appreciated.

Michael S Palatsi
  • 91
  • 1
  • 9
  • Are you able to open that path from the SQL Server machine? SharePoint is *not* a file server. It exposes a WebDAV endpoint that can be treated as a "network share" by Windows *provide* the `Web Folders` service is running – Panagiotis Kanavos Aug 27 '20 at 15:20
  • @PanagiotisKanavos I am not allowed to remote into the SQL Server machine to test that unfortunately. – Michael S Palatsi Aug 27 '20 at 15:22
  • How do you know that WebDAV would work at all on that server then? – Panagiotis Kanavos Aug 27 '20 at 15:25
  • @PanagiotisKanavos I do not, to further that point, if I login to SSMS as the service account and run "EXEC master.dbo.xp_fileexist [PATH]", it will show that the directory does not exist for that account. Do you know of a solution? I'm open to alternative methods to accomplish my goal. – Michael S Palatsi Aug 27 '20 at 16:19
  • without access to log onto the sever as your service account you wont be able to get this to work. Even if you could, it's unstable. You have to install “WebDav Redirector” on the server, add the sharepoint site to trusted list in IE, log into sharepoint and tick the box "remember me", etc... But at some point sharepoint wants you to re-authenticate. Spent weeks sorting it out. We just use power shell scripts in the agent job step to download the file local prior to running SSIS. And that has to be sql2014 or later due to what version of powershell is used by sql agent. – Tim Mylott Aug 27 '20 at 17:02
  • @TimMylott Would you mind showing me the powershell you used? – Michael S Palatsi Aug 27 '20 at 17:10
  • I was also going to note using powershell has it's challenges as well. Someone would still need to log onto the SQL server that has admin rights and run powershell console as Administrator to install the sharepoint modules. This only needs to be done once. Service account for sharepoint has to be in site owner and permissions to the specific folder in sharepoint. Then you still have to have that service account and password in the script, it does not or can't pass the proxy account credentials. – Tim Mylott Aug 27 '20 at 17:44

1 Answers1

1

As mentioned in my comment we did not have much luck or stability in using the SharePoint UNC with SSIS when deployed to a server. We ended up using a PowerShell script in an agent job step to download the file local prior to the SSIS step.

This only works on SQL Server 2014 or later due to the version of PowerShell used by agent and the need to install the SharePoint module.

  1. You'll need admin rights to the SQL server or whomever manages it to log on and run PowerShell as Administrator.

enter image description here

  1. Then run the following command with "yes" to any prompts:

Install-Module SharePointPnPPowerShellOnline

enter image description here

  1. If you get an error running that command with something as connection closed or unable to communicate it can be been related to Tls12 not enabled in PowerShell. Run following command to enable the Tls12 protocol:

[Net.ServicePointManager]::SecurityProtocol =[Net.SecurityProtocolType]::Tls12

  1. Then rerun the "Install-Module SharePointPnPPowerShellOnline" again and it should succeed now.

Once you have completed that, we setup a service account specifically for this and found that the account needed to be apart of the "Site Owner" group for the SharePoint site you are downloading files from.

Then add a PowerShell Agent Job step using the following code to download the files local for SSIS. Update for your environment.

$SharepointBaseURL = "https://yoursharepoint.com/sites/sitename/" #base URL of your site
$SharepointDocumentFolder = "Shared Documents/path to folder" #path to the folder where the files are located

$LocalShare = "\\server\localshare"  #where you download local, sql proxy account needs access


$un = "YourAccount@domain.com"
$pw = "Password"

Set-Location "c:\"  #we had to have this when running in agent job

try
{
    $sp = $pw | ConvertTo-SecureString -AsPlainText -Force
    $plainCred = New-Object system.management.automation.pscredential -ArgumentList $un, $sp

    Connect-PnPOnline -Url $SharepointBaseURL -Credentials $plainCred -ErrorAction Stop
    $SharePointFileList = Get-PnPFolderItem -FolderSiteRelativeUrl $SharepointDocumentFolder -ItemType File #gets a list of all files in the sharepoint directory

    foreach ($File in $SharePointFileList)
    {
        Get-PnPFile -Url $File.ServerRelativeUrl -Path $LocalShare -Filename $File.Name -AsFile -ErrorAction Stop #Add "-Force" parameter if you want to override if the file already exists
    }

}
Catch
{
    Throw $_.Exception.Message #any errors/exceptions this bubbles it out into job history
}

From there SSIS is then configured to access the local file.

Tim Mylott
  • 2,553
  • 1
  • 5
  • 11