This is the visual basic Script to import multiple excel files with different sheet names into single SQL table. Here is the Steps to be followed:-
Step 1:- Create a multiple excel sheets with different name and different sheet name.
NOTE:- All Excel files and sheets have same columns.
Step 2:- Create a SQL table which has same attributes as Excel sheet.
Step 3:- Open SSIS project and then create or open package.Create 4 variables for example
@FolderPath- Set value according to the folder where you stored your Excel File for example C:/Excel/
@FileName- Set value according to file name, make sure that this string have complete path for example C:/Excel/ExcelFile1.xls.
@SheetName- This variable have only sheet name for example ABC$.
@FileExtension- Set the file extension for example *.xls
Step 4:-Create Connection Manager.
Excel Connection Manager- Set the expression property to Connection String and value as @FileName.
OLEDB Connection Manager- Set Connection with your SQl Table
Step4:-In Control Flow Task drag For Each loop. In For Each Loop Container drag Script Task and data flow task.connect script Task and data flow task.
Step 5:- Enumerate For Each Loop container with with folder as @Folderpath and files @FileExtension
In Variable mapping select @FileName as Variable and index 0
Step 6:- Edit script Task make sure that you are selecting Scripting Language as MIcrosoft Visual Basic
Set Read only Variable to @FolderPath which you have created.
Set Read and Write Variable to @FileName and @SheetName
Click on Edit Scrit and write the code given below(Make changes according to your requirement).
Public Sub Main()
Dim excelFile As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim tablesInFile As DataTable
Dim tableCount As Integer = 0
Dim tableInFile As DataRow
Dim currentTable As String
Dim tableIndex As Integer = 0
Dim excelTables As String()
Try
excelFile = Dts.Variables("@FileName").Value.ToString
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFile + ";Extended Properties=""EXCEL 12.0 XML;HDR=YES"";"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
ReDim excelTables(tableCount - 1)
For Each tableInFile In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
currentTable = currentTable.Replace("'", "")
If Right(currentTable, 1) = "$" Then
excelTables(tableIndex) = currentTable
tableIndex += 1
End If
Next
Dts.Variables("@SheetName").Value = excelTables(0)
excelConnection.Close()
Catch Ex As Exception
MessageBox.Show(Ex.Message)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Step 7:- Goto Data flow Task and Drag a excel Source and OLEDB Destination. Make Connections according to connection Manager.
Step 8:- Edit Excel Source and set properties
AccesMode- OpenRowSet From Variable
RowsetVariable- @SheetName
Step 9:- Save the package and execute.