SQL 在不知道工作表名称的情况下使用 SSIS 从 Excel 导入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4444169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import data from Excel using SSIS without knowing sheet name
提问by Ken Pespisa
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.
我有一个由另一台服务器更新的电子表格（我无法控制），我需要自动将该数据导入 SQL 2005。数据始终是电子表格的第一页。但是，该工作表的名称会根据行数而变化。
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.
有没有办法在不知道工作表名称的情况下运行从 Excel 中提取数据的 SSIS 作业？它似乎依赖于工作表名称作为数据源，但我希望告诉它“工作表编号 1”或类似的东西。
采纳答案by AdamA
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)
我会将工作表名称编写为 SSIS 用户变量。如果您不反对将脚本任务插入到 SSIS 包中，请尝试以下操作：（基于链接文本）
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 */
回答by OzrenTkalcecKrznaric
Just for the record, I'm using this code in Script Task to solve the problem. Variables used are: Filename, SheetName.
只是为了记录，我在脚本任务中使用此代码来解决问题。使用的变量是：Filename，SheetName。
Note that my Excel filename is dynamic.
请注意，我的 Excel 文件名是动态的。
// GET NAME OF FIRST SHEET
string filename = (string)Dts.Variables["Filename"].Value;
string sheetName = null;
string connStr =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;IMEX=1;\"", filename);
var conn = new OleDbConnection(connStr);
try
{
conn.Open();
using(var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
var row0 = dtSheet.Rows[0];
sheetName = row0["TABLE_NAME"].ToString();
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
if (!String.IsNullOrEmpty(sheetName))
{
Dts.Variables["SheetName"].Value = sheetName;
Dts.Events.FireInformation(1, "User::SheetName", sheetName, "", 0, ref dummy);
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.Events.FireError(0, "User::SheetName", "No SheetName found!", String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
回答by Abhishek Gohil
I had a similar problem. The solution that I implemented was first read the excel file using OleDB connection. Open the connection and then retrieve all the sheet names. Here is an example
我有一个类似的问题。我实现的解决方案是首先使用 OleDB 连接读取 excel 文件。打开连接，然后检索所有工作表名称。这是一个例子
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ABC.xls;Extended Properties=""EXCEL 8.0;"""
Dim lstSheetName As List(Of String) = Nothing
Try
objConn = New OleDbConnection(Me.ConnectionString)
objConn.Open()
lstSheetName = New List(Of String)
Using dtSheetTable As DataTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,Nothing)
For Each drRow As DataRow In dtSheetTable.Rows
lstSheetName.Add("[" & drRow("TABLE_NAME").ToString().Replace("'", "''") & "]")
Next
End Using
Catch ex as Exception
Throw
Finally
If objConn.State = ConnectionState.Open Then objConn.Close()
objConn.Dispose()
End Try
This all code is written ASPX.VB and then I am executing the SSIS package through code behind and passing the first value in the lstSheetName variable (lstSheetName(0).ToString())
这所有的代码都是写 ASPX.VB 然后我通过后面的代码执行 SSIS 包并传递 lstSheetName 变量中的第一个值 (lstSheetName(0).ToString())
This was
这是
回答by Chris B. Behrens
I don't think so...I don't know of any ordinal reference syntax, e.g., Sheets[0] that you could use.
我不这么认为......我不知道您可以使用的任何序数参考语法，例如 Sheets[0] 。
So if you can't get the data without knowing the sheet name - you just need to dynamically find out the sheet name. This link on getting Excel schema info in SSISshould help you do that. Once you have that, you can pass the sheet name in as a variable, and away you go.
因此，如果您在不知道工作表名称的情况下无法获取数据 - 您只需要动态找出工作表名称即可。这个关于在 SSIS 中获取 Excel 架构信息的链接应该可以帮助你做到这一点。一旦你有了它，你就可以将工作表名称作为变量传入，然后就可以了。
回答by Matt Cupp
I have had this same issue myself in the past and was unable to find a solution to having an Excel file be read in which has its sheet name change from file to file.
过去我自己也遇到过同样的问题，并且无法找到读取 Excel 文件的解决方案，该文件的工作表名称从文件到文件都发生了变化。
My guess, which I was unable to get to work, would be to use expressions in the properties of the data connection. You would need to somehow read the sheet name into a variable, then use that variable's result in the sheet name for the data connection.
我无法开始工作，我的猜测是在数据连接的属性中使用表达式。您需要以某种方式将工作表名称读入变量，然后在数据连接的工作表名称中使用该变量的结果。
Best of luck to you, and sorry I couldn't be of more help.
祝你好运，很抱歉我帮不上忙。
回答by user3393344
If anyone has trouble with the JET Driver you can use the AccessDatabase drivers now. This was adapted from above and is verified working on my machine, no extra references are needed for this.
如果有人在使用 JET 驱动程序时遇到问题，您现在可以使用 AccessDatabase 驱动程序。这是从上面改编的，并在我的机器上进行了验证，不需要额外的参考。
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
public void Main()
{
// GET NAME OF FIRST SHEET
string filename = Dts.Variables["User::ActiveFileName"].Value.ToString();
string sheetName = null;
bool dummy = true;
string connStr =
String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";", filename);
var conn = new OleDbConnection(connStr);
try
{
conn.Open();
using(var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
var row0 = dtSheet.Rows[0];
sheetName = row0["TABLE_NAME"].ToString();
}
if (!String.IsNullOrEmpty(sheetName))
{
Dts.Variables["SheetName"].Value = sheetName;
Dts.Events.FireInformation(1, "User::SheetName", sheetName, "", 0, ref dummy);
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
throw new Exception("No SheetName found!");
}
}
catch (Exception ex)
{
Dts.Events.FireError(0, "User::SheetName", ex.Message, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
conn.Close();
conn.Dispose();
}
}
