Link to home
Start Free TrialLog in
Avatar of skaleem1
skaleem1Flag 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
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
good, you did it correctly.
Regards,