Q: SSIS: Accessing Sharepoint UNC via SQL Server Agent Job 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.

A: 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.

*

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




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


Install-Module SharePointPnPPowerShellOnline




*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



*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.
