Avatar of skaleem1
skaleem1
Flag for Canada asked on

SSIS - How to dynamically import data from multiple spreadsheets to multiple SQL Server tables

This qusetion is to refund extra points to Riza_rad he deserves:

https://www.experts-exchange.com/questions/25134714/How-to-best-download-multiple-Excel-Files-and-insert-data-into-SQL-Server-database-tables.html?anchorAnswerId=26614988#a26614988

Riza_rad, please reply and I will accept the reply as a solution,

Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Reza Rad

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Reza Rad

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
skaleem1

ASKER
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)

        Dts.Variables("User::DestTableName").Value = FullUrlString


        ' 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 + "$]')"

        connection.DownloadFile(filename, True)


        Dts.TaskResult = Dts.Results.Success
    End Sub


End Class
Reza Rad

good, you did it correctly.
Regards,

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck