ssis:基于web查询结果以编程方式创建新的excel文件-如何保存web查询表名?

我想做什么?

我有一个url列表,我想用excel的web查询功能来抓取。我正试图完全自动化这个过程,所以我正在开发一个ssis包,为每个url调用一个脚本任务。脚本任务使用工作表创建新的Excel工作簿,激活工作表,添加可查询连接,刷新可查询表以获取数据,使用 XlWebSelectionType.xlAllTables . 然后保存工作簿并关闭工作簿和Excel应用程序。

我在利用什么技术?

• 与2015年相比(企业)
• SQL Server 2016版
• Microsoft Excel 16.0对象库
• 从Office 365 ProPlus进行Excel本地安装

怎么了?

当脚本任务保存网页上表中的所有数据时,它会将它们全部放入单个工作表中,而不会保存表名。因此,虽然我的数据在工作表中正确分组,但我无法知道哪个“组”数据对应于哪个表。

我想怎么办?

理想情况下,我希望将每个可查询表保存到其自己的工作表中,并将表名设置为工作表名。除此之外,我需要一种将表名与相应数据一起保存的方法。在这个场景中,将它作为新列添加到querytable中是最好的。

到目前为止我有什么?

这是剧本的主要部分:

Public Sub Main()
    Dim URL As String = Dts.Variables("User::URL").Value.ToString()
    Dim FileName As String = Dts.Variables("User::FileName").Value.ToString()
    Dim xlNone As XlWebFormatting = XlWebFormatting.xlWebFormattingNone
    Dim Format As XlFileFormat = XlFileFormat.xlCSVWindows
    Dim ScrapeStatus As Integer = 1

    Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass

    With excel
        .SheetsInNewWorkbook = 1
        .DisplayAlerts = False
    End With

    Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add()

    With wb
        .Activate()
        .Worksheets.Select(1)
    End With

    Try

        Dim rnStart As Range = wb.ActiveSheet.Range("A1:Z100")
        Dim qtQtrResults As QueryTable = wb.ActiveSheet.QueryTables.Add(Connection:="URL;" + URL, Destination:=rnStart)

        With qtQtrResults
            .BackgroundQuery = False
            .WebFormatting = xlNone
            .WebSelectionType = XlWebSelectionType.xlAllTables
            .Refresh()
        End With

        excel.CalculateUntilAsyncQueriesDone()
        wb.SaveAs(FileName)

        wb.Close()
        excel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        GC.Collect()
        GC.WaitForPendingFinalizers()
        Dts.TaskResult = ScriptResults.Success

    Catch ex As Exception

        Dts.Variables("User::Error").Value = ex.Message.ToString()
        wb.Saved = True
        wb.Close()
        excel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        GC.Collect()
        GC.WaitForPendingFinalizers()
        Dts.TaskResult = ScriptResults.Failure

    End Try

End Sub

我得到了什么结果?

对于URL http://athletics.chabotcollege.edu/information/directory/home#directory ,如果在Excel中使用Web查询功能,则可以从中选择以下选项: 将显示所有表名

但是,当我通过脚本任务拉取所有表时,最终会得到一个类似于以下内容的工作表:

其他信息

我还应该注意到,虽然大多数网页的结构相似,但并非所有网页都相同。所以我不能假设每个页面都有相同的表名,或者以相同的方式构造表。我的解决方案需要是动态的和灵活的。