为了使用SSIS读取EXCEL中的全部sheet,操作步骤如下:
1. 建立package级别的变量:
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 New ArrayList()
excelFile = Dts.Variables("ExcelFile").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
For Each tableInFile In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
If (currentTable.StartsWith("'Hotmail UTS") Or currentTable.StartsWith("'TV UTS")) And currentTable.EndsWith("'") Then
excelTables.Add(currentTable)
Dts.Log(currentTable, 22, Nothing)
tableIndex += 1
End If
Next
Dts.Variables("ExcelTables").Value = excelTables
Dts.TaskResult = Dts.Results.Success
3. 拖放一个Foreach Loop组件,连接在脚本组件后,并做以下设置
5. 在数据流中放上EXCEL源数据组件
配置如下:
1. 建立package级别的变量:
- ExcelFile EXCEL数据文件名
- ExcelTables Excel Sheets集合变量
- TableName 当前读取的SHEET名称
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 New ArrayList()
excelFile = Dts.Variables("ExcelFile").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
For Each tableInFile In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
If (currentTable.StartsWith("'Hotmail UTS") Or currentTable.StartsWith("'TV UTS")) And currentTable.EndsWith("'") Then
excelTables.Add(currentTable)
Dts.Log(currentTable, 22, Nothing)
tableIndex += 1
End If
Next
Dts.Variables("ExcelTables").Value = excelTables
Dts.TaskResult = Dts.Results.Success
3. 拖放一个Foreach Loop组件,连接在脚本组件后,并做以下设置
- 枚举器为For each from variables
- 变量为ExcelTables
- 变量映射 User:TableName = Param 0
5. 在数据流中放上EXCEL源数据组件
配置如下:
- 数据访问模式 - 表名或者视图名称变量
- 变量名 - TableName
- ValidateExternalMetadata - False