在不知道工作表名称的情况下使用SSIS从Excel导入数据

问题描述

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

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.

是否有一种方法可以运行SSIS作业，从而无需事先知道工作表名称就可以从Excel中提取数据?它似乎依赖于工作表名称作为数据源，但我想告诉它工作表编号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, "\t", 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导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！