如何使用ssis中的脚本任务将具有不同工作表名称的多个excel文件导入到一个sql表中 [英] How to import multiple excel files with different sheet names into one sql table using script task in ssis

问题描述

我想使用SSIS中的脚本任务将多个具有不同工作表名称的excel文件导入到一个SQL表中。



请帮助我。我非常感谢你。

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.

推荐答案

这是将具有不同工作表名称的多个excel文件导入单个SQL表的可视化基本脚本。以下是要遵循的步骤： -



步骤1： - 创建具有不同名称和不同工作表名称的多个Excel工作表。

注意： - 所有Excel文件和工作表都有相同的列。



步骤2： - 创建一个与Excel工作表具有相同属性的SQL表。



步骤3： - 打开SSIS项目然后创建或打开package.Create 4变量例如

@ FolderPath-根据您存储的文件夹设置值Excel文件例如C：/ Excel /

@FileName-根据文件名设置值，确保该字符串具有完整路径，例如C：/Excel/ExcelFile1.xls。

@ SheetName-此变量只有工作表名称，例如ABC

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-设置文件扩展名，例如* .xls



步骤4：创建连接管理器。

Excel连接管理器 - 将表达式属性设置为连接字符串，将值设置为@FileName。

OLEDB连接管理器 - 设置与SQl表的连接

步骤4：-In控制流任务拖动For Each循环。在For Each Loop Container中，拖动脚本任务和数据流task.connect脚本任务和数据流任务。



步骤5： - 枚举每个循环容器，文件夹为@Folderpath和文件@FileExtension

在变量映射中选择@FileName as变量和索引0



步骤6： - 编辑脚本任务确保您选择脚本语言为MIcrosoft Visual Basic

设置只读变量为您创建的@FolderPath。

将读取和写入变量设置为@FileName和@SheetName



单击编辑Scrit并写入下面给出的代码（根据您的要求进行更改）。

.
@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) = "

那么
excelTables（tableIndex）= currentTable

tableIndex + = 1
结束如果
下一个

Dts.Variables（@ SheetName）。Value = excelTables（0）
excelConnection.Close（）
Catch Ex As Exception
MessageBox.Show（Ex.Message）

结束尝试
Dts.TaskResult = ScriptResults.Success
End Sub

" 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

步骤7： - 转到数据流任务并拖动excel Source和OLEDB Destination。根据连接管理器建立连接。



步骤8： - 编辑Excel源并设置属性

AccesMode- OpenRowSet来自变量

RowsetVariable- @ SheetName



步骤9： - 保存包并执行。

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.

这篇关于如何使用ssis中的脚本任务将具有不同工作表名称的多个excel文件导入到一个sql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！