excel - 在不知道 sheename 的情况下使用 SSIS 导入 EXCEL

Question

我正在尝试使用 SSIS 从一个文件夹中导入多个文件，但我不知道 SheetName .

所以，我正在根据下面的链接创建一个脚本任务，以获取 SheetName ，但我在脚本任务“不能在变量声明中指定数组大小”中遇到错误

http://www.anupamanatarajan.com/2011/01/dynamic-sheet-name-in-ssis-excel.html

public void Main()
        {
            // TODO: Add your code here


            string excelFile = null;
            string connectionString = null;
            OleDbConnection excelConnection = null;
            DataTable tablesInFile = null;
            int tableCount = 0;
            DataRow tableInFile = null;
            string currentTable = null;
            int tableIndex = 0;
            string[] excelTables = null;



            excelFile = Dts.Variables["User::BBGFilePath"].Value.ToString();

            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";

            excelConnection = new OleDbConnection(connectionString);

            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");

            tableCount = tablesInFile.Rows.Count;
            excelTables = new string[tableCount];

            foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
            {
                tableInFile = tableInFile_loopVariable;
                currentTable = tableInFile["TABLE_NAME"].ToString();
                excelTables[tableIndex] = currentTable;
                tableIndex += 1;
            }
        }

            //Provide value to the shetename variable
            Dts.Variables["User::SheetName"].Value = excelTables[0];



            //Display file name
            string strMessage = Dts.Variables["User::BBGFilePath"].Value.ToString();
            MessageBox.Show(strMessage);


            Dts.TaskResult = (int)ScriptResults.Success;
        }

所以我尝试添加 [User:SheetName] Script task 的变量，但它不起作用。

任何人都可以请检查缺少什么？

Answer 1

正如我之前提到的，该错误确实表明您在类级别有一些无效的非声明语句。

您的脚本任务中的代码在右大括号方面存在一些问题——

public void Main()
        {
            // TODO: Add your code here

            string excelFile = null;
            string connectionString = null;
            OleDbConnection excelConnection = null;
            DataTable tablesInFile = null;
            int tableCount = 0;
            DataRow tableInFile = null;
            string currentTable = null;
            int tableIndex = 0;
            string[] excelTables = null;

            excelFile = Dts.Variables["User::BBGFilePath"].Value.ToString();

            //Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\CESLtd\ELKAY\Reports\Work2\Book1.xls; Extended Properties = "EXCEL 8.0;HDR=YES";
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;HDR=YES";

            excelConnection = new OleDbConnection(connectionString);

            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");

            tableCount = tablesInFile.Rows.Count;
            excelTables = new string[tableCount];

            foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
            {
                tableInFile = tableInFile_loopVariable;
                currentTable = tableInFile["TABLE_NAME"].ToString();
                excelTables[tableIndex] = currentTable;
                tableIndex += 1;
            }
        //} **commented this line now you are good to go**

            //Provide value to the shetename variable
            Dts.Variables["User::SheetName"].Value = excelTables[0];



            //Display file name
            string strMessage = Dts.Variables["User::BBGFilePath"].Value.ToString();
            MessageBox.Show(strMessage);

            Dts.TaskResult = (int)ScriptResults.Success;

        }