Click here to Skip to main content
15,940,246 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to import multiple excel files with different sheets name into one SQL table using script task in SSIS.

Please help me. I am very Thankful to you.
Posted
Updated 2-May-14 3:28am
v2
Comments
Maciej Los 2-May-14 9:31am    
What have you tried so far?
Piyush Jain618 5-May-14 2:42am    
I had try to done it with script task using vb code.

1 solution

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.
 
Share this answer
 
v2
Comments
Member 12887760 11-Jan-19 10:25am    
First of all, thank you for this solution. But when I'm using it I have error message on my package telling me that sheetName doesn't exist. The variable SheetName can't have the value of the Sheet_Name. What information do you need ? there is any solution to load the first Excel Sheet without using VB script ? Thank you upfront for your feedback.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900