Downloading Excel files from SharePoint using SSIS
In my previous post, I outlined how to obtain a recursive list of all Excel files held in a SharePoint document library. In SSIS, I use this code to upsert records into a table which identifies which files are new or modified. Clearly I could process the file in-situ, but that would over complicate my code with data streams which SSIS would not understand – especially if you are attempting to use the standard Microsoft.Jet.OLEDB.4.0 engine to process the file! So downloading the file to a local folder is the easiest option. Fortunately, I spotted Greg Enslow's post which pointed me in the direction of using the WebClient library.
In my control flow I obtain a list all the files I need to process and store it in an ADO.NET recordset. I then iterate over this list using a ForEach task, which sets various variables containing the URL, file extension and the document's name and it's GUID in SharePoint. My script task then downloads each file so it can be processed by subsequent tasks in the control flow.
I have expanded on Greg's original code to support file system locations as well as SharePoint URLs. Enjoy!
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Public
Class ScriptMain
Public
Sub Main()
' downloads the file from SharePoint or a file system location to a local folder
Dim taskResult As
Integer = Dts.Results.Success
Try
' obtain location of local folder from variable
Dim dir As DirectoryInfo = New DirectoryInfo(Dts.Variables("ImportFolder").Value.ToString())
If dir.Exists Then
' Create the filename for local storage using
' the GUID from SharePoint as this will be unique.
Dim file As FileInfo = New FileInfo(dir.FullName & "\" & Dts.Variables("WorkbookGUID").Value.ToString() & Dts.Variables("Extension").Value.ToString())
If
Not file.Exists Then
' get the path of the file we need to download
Dim fileUrl As
String = Dts.Variables("EncodedAbsUrl").Value.ToString()
If fileUrl.Length <> 0 Then
' download the file from SharePoint or Archive file system to local folder
Dim client As
New WebClient()
If Left(fileUrl, 4).ToLower() = "http"
Then
'download the file from SharePoint
client.Credentials = System.Net.CredentialCache.DefaultCredentials
client.DownloadFile(fileUrl, file.FullName)
Else
' copy file from remote file system
System.IO.File.Copy(fileUrl, file.FullName)
End
If
Else
Throw
New ApplicationException("EncodedAbsUrl variable does not contain a value!")
End
If
End
If
Else
Throw
New ApplicationException("ImportFolder does not exist!")
End
If
Catch ex As Exception
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
taskResult = Dts.Results.Failure
End
Try
Dts.TaskResult = taskResult
End
Sub
End
Class