Start Free Trial
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:
Riza_rad, please reply and I will accept the reply as a solution,
Microsoft SQL Server
Microsoft SQL Server 2005
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
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.
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)::
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
artIndex + FindTemplate.Length)
Dim endIndex As Integer = FullUrlString.IndexOf(".xl
FullUrlString = FullUrlString.Remove(endIn
.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::FileP
() + System.IO.Path.DirectorySe
r + FullUrlString + ".xls"
'Dts.Connections("HTTP Connection Manager").ReleaseConnectio
Dim SheetName As String
Select Case FullUrlString
SheetName = "MaterialGroup"
SheetName = "Parts"
SheetName = "PartsDocMapping"
SheetName = "VendorMapping"
SheetName = "BOM"
SheetName = "WhereUsed"
ue = "INSERT INTO " + FullUrlString + " SELECT * FROM OPENROWSET('Microsoft.Jet.
,'Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"
Dts.TaskResult = Dts.Results.Success
good, you did it correctly.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
Plans and Pricing
Certified Expert Program
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent