Accessing Sharepoint Document Library – SSIS Script task
The image below shows us how incredibly simple and clean the package will look when finished. There are some things worth pointing out at this stage. In the centre of the screen we see the Script task, Foreach Enumerator container and inside that we see the Data Flow task which houses the pipeline. At the bottom in the Connection Managers tray we see our Excel File Connection Manager (Customer List) and our OLEDB Connection Manager (CIT Global Destination). The Excel File Connection Manager is the one in which we are most interested for this article. Both of these managers are used in the Data Flow behind the DataFlow task. We will not be detailing the pipeline behind the DataFlow task in this article but it consists of a Excel Source moving data to an OLEDB destination.
Script task code
Option Strict Off
Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports Microsoft.VisualBasic.Compatibility.VB6
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Dim TheFile As String
Dim DestFolder As String
Dim URL As String
'Dim xml As MSXML.XMLHTTPRequest
Public Sub Main()
Try
TheFile = "CustomerList.xls"
DestFolder = "C:\SSIS_Sources\Customer List\"
URL = "http://citsharepoint/portals/SM/Reports/Shared Documents/Customer List/CustomerList.xls"
Dim xml = CreateObject("Microsoft.XMLHTTP")
xml.Open("GET", URL, False)
xml.Send()
Dim oStream = CreateObject("Adodb.Stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adSaveCreateNotExist = 1
oStream.type = adTypeBinary
oStream.open()
oStream.write(xml.responseBody)
' Overwrite an existing file
oStream.savetofile(DestFolder & TheFile, adSaveCreateOverWrite)
oStream.close()
oStream = Nothing
xml = Nothing
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
End Try
End Sub
End Class
Note. Mandatory steps
1. Sharepoint access.
2. Destination Folder.
