Yes. In my script task script (please note (1) how I have changed the query to Insert Into as the tables would always be existing and (2) all sheet names are different from the different files so I am taking this into consideration)::
Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
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.
Public Sub Main()
' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
'exteract filename for urlstring
Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
Dim FindTemplate As String = "outputFileName="
Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
FullUrlString = FullUrlString.Remove(endIndex)
' Download the file #1
' Save the file from the connection manager to the local path specified
Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString + ".xls"
'Dts.Connections("HTTP Connection Manager").ReleaseConnection(nativeObject)
Dim SheetName As String
Select Case FullUrlString
Case "AccMaterialHierarchySource"
SheetName = "MaterialGroup"
Case "AccAllPartsSource"
SheetName = "Parts"
Case "AccPartsDocMappingSource"
SheetName = "PartsDocMapping"
Case "AccVendorMappingSource"
SheetName = "VendorMapping"
Case "AccBomSource"
SheetName = "BOM"
Case "AccWhereUsedSource"
SheetName = "WhereUsed"
End Select
Dts.Variables("User::SqlString").Value = "INSERT INTO " + FullUrlString + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"
Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Ru
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.
Public Sub Main()
' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnectio
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativ
'exteract filename for urlstring
Dim FullUrlString As String = Dts.Variables("User::UrlSt
Dim FindTemplate As String = "outputFileName="
Dim startIndex As Integer = FullUrlString.IndexOf(Find
FullUrlString = FullUrlString.Substring(st
Dim endIndex As Integer = FullUrlString.IndexOf(".xl
FullUrlString = FullUrlString.Remove(endIn
Dts.Variables("User::DestT
' Download the file #1
' Save the file from the connection manager to the local path specified
Dim filename As String = Dts.Variables("User::FileP
'Dts.Connections("HTTP Connection Manager").ReleaseConnectio
Dim SheetName As String
Select Case FullUrlString
Case "AccMaterialHierarchySourc
SheetName = "MaterialGroup"
Case "AccAllPartsSource"
SheetName = "Parts"
Case "AccPartsDocMappingSource"
SheetName = "PartsDocMapping"
Case "AccVendorMappingSource"
SheetName = "VendorMapping"
Case "AccBomSource"
SheetName = "BOM"
Case "AccWhereUsedSource"
SheetName = "WhereUsed"
End Select
Dts.Variables("User::SqlSt
connection.DownloadFile(fi
Dts.TaskResult = Dts.Results.Success
End Sub
End Class