SSIS 2005 - Script Task to Import Excel file from SharePoint

Use the below VB script in SSIS Script task to Import Excel file from SharePoint:


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