我有一个ssis包,它接受一个excel文件并导入它,但是如果工作表中缺少任何列,则导入失败。
我试图编写一些代码来检查工作表中的列标题,以确保它包含一个列的集合列表,只要它们存在,它就不需要检查是否有正确的顺序。
到目前为止,我所掌握的代码如下
Dim strFile As String
strFile = Dts.Variables("User::found_file").Value.ToString
Dim xlConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
strFile & ";Extended Properties=""Excel 12.0 XML;HDR=YES"""
Dim xlConnection As New OleDbConnection(xlConnectionString)
xlConnection.Open()
Dim tablesInFile As DataTable = xlConnection.GetSchema("TABLES")
Dim currentTable As String
Dim columnsInTable As DataTable
Dim columnRestrictions(3) As String
Dim columnInTable As DataRow
Dim currentColumn As String
For Each tableInFile As DataRow In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
'tray header
If currentTable = "'Tray Header$'" Then
columnRestrictions(2) = currentTable
columnsInTable = xlConnection.GetSchema("COLUMNS", columnRestrictions)
end if
next 我需要一种简单的方法来检查所有列,以确保它们都存在,而不必执行一个循环,每次检查每个列。
如果缺少列,我需要它将失败布尔值标记为true。
“托盘标题”工作表包含的列是“托盘”、“名称”、“描述”、“数量”。
发布于 2015-06-03 15:48:31
您可以使用简单的SELECT来验证您的文件:
Dim blnMissingColumns As Boolean = False
Dim cmdTest As OleDbCommand = xlConnection.CreateCommand
cmdTest.CommandText = "SELECT TOP 1 trayid, trayname, description, quantity FROM [Tray Header$]"
Try
cmdTest.ExecuteNonQuery()
Catch ex As Exception
If TypeOf ex Is OleDbException Then
If CType(ex, OleDbException).ErrorCode = -2147217904 Then
blnMissingColumns = True
End If
End If
End Try
cmdTest.Dispose()https://stackoverflow.com/questions/30622637
复制相似问题