SSIS 2008 - Script task to Import Excel file from SharePoint

Use the below VB script to import excel file from SharePoint in SSIS 2008 Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 
 ' The execution engine calls this method when the task executes.
 ' To access the object model, use the Dts property. Connections, variables, events,
 ' and logging features are available as members of the Dts property as shown in the following examples.
 '
 ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
 ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
 ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
 '
 ' To use the connections collection use something like the following:
 ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
 ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
 '
 ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
 '
 ' To open Help, press F1.

    Public Sub Main()
        Dim URL As String = "http://citind:28000/Excel%20Library/HelpdeskSoftware/Software%20usage%20report.xls"
        Dim Req As System.Net.HttpWebRequest = DirectCast(System.Net.WebRequest.Create(URL), System.Net.HttpWebRequest)
        'Req.Credentials = System.Net.CredentialCache.DefaultCredentials
        Req.Credentials = New System.Net.NetworkCredential("datawh", "500%sec", "cit")
        Req.Method = "GET"
        Dim path As String = "C:\DataFiles\HelpDesk\Software usage report.xls"
        Dim objResponse As System.Net.WebResponse = Req.GetResponse()
        Dim fs As New System.IO.FileStream(path, System.IO.FileMode.Create)
        Dim stream As System.IO.Stream = objResponse.GetResponseStream()
        Dim buf As Byte() = New Byte(1023) {}
        Dim len As Integer = stream.Read(buf, 0, 1024)
        While len > 0
            fs.Write(buf, 0, len)
            len = stream.Read(buf, 0, 1024)
        End While
        stream.Close()
        fs.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class