在不知道工作表名称的情况下使用 SSIS 从 Excel 导入数据 [英] Import data from Excel using SSIS without knowing sheet name

问题描述

我有一个由另一台服务器更新的电子表格(我无法控制)，我需要自动将该数据导入 SQL 2005.数据始终是电子表格的第一页.但是，该工作表的名称会根据行数而变化.

I have a spreadsheet that is updated by another server (out of my control) and I need to automate bringing that data into SQL 2005. The data is always the first page of the spreadsheet. However, the name of that sheet changes depending on the number of rows.

有没有办法在不知道工作表名称的情况下运行从 Excel 中提取数据的 SSIS 作业?它似乎依赖于工作表名称作为数据源，但我希望告诉它工作表编号 1"或类似的内容.

Is there a way to run an SSIS job that pulls in data from Excel without knowing the sheetname beforehand? It seems to rely on the sheet name as the data source, but I'm looking to tell it "sheet number 1" or something similar.

推荐答案

我会将工作表名称编写为 SSIS 用户变量.如果您不反对将脚本任务插入到 SSIS 包中，请尝试以下操作:(基于 链接文本 )

I would script out the Worksheet name to a SSIS User Variable. If you are not opposed to inserting a script task into your SSIS package try this: (Based on link text )

Excel.Application xlApp = new Excel.ApplicationClass();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("<Name of your excel app>.xls", 0, xlWorkBook true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "	", false, false, 0, true, 1, 0);
// Look up worksheet by index
Excel.Worksheet xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

user::worksheetname = xlWorkSheet.Name;

/* Do clean up.  Working with COM object */

这篇关于在不知道工作表名称的情况下使用 SSIS 从 Excel 导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！