在不知道 sheename 的情况下使用 SSIS 导入 EXCEL [英] Import EXCEL with SSIS without knowing sheename

问题描述

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

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

解决方案

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

您的脚本任务代码的右大括号存在一些问题 --

public void Main(){//TODO: 在此处添加您的代码字符串 excelFile = null;字符串 connectionString = null;OleDbConnection excelConnection = null;数据表tablesInFile = null;int tableCount = 0;DataRow tableInFile = null;字符串 currentTable = null;int tableIndex = 0;字符串[] excelTables = null;excelFile = Dts.Variables["User::BBGFilePath"].Value.ToString();//Provider = Microsoft.Jet.OLEDB.4.0;数据源 = C:\CESLtd\ELKAY\Reports\Work2\Book1.xls;扩展属性 = "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();tableInFile = excelConnection.GetSchema("表格");tableCount = tablesInFile.Rows.Count;excelTables = new string[tableCount];foreach(tableInFile.Rows 中的 DataRow tableInFile_loopVariable){tableInFile = tableInFile_loopVariable;currentTable = tableInFile["TABLE_NAME"].ToString();excelTables[tableIndex] = currentTable;表索引 += 1;}//} **评论此行现在你可以走了**//为shetename变量提供值Dts.Variables["User::SheetName"].Value = excelTables[0];//显示文件名string strMessage = Dts.Variables["User::BBGFilePath"].Value.ToString();MessageBox.Show(strMessage);Dts.TaskResult = (int)ScriptResults.Success;}

I'm trying to use SSIS to import multiple files from a folder, and i dont know the SheetName.

So, I'm creating a script task according to below link, to get SheetName, but i got error in the script task 'array size cannot be specified in a variable declaration'

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;
        }

So i tried to add the [User:SheetName] variable to the Script task, but it doesn't work.

can anyone please check what is missing?

解决方案

As I had mentioned earlier, the error does clearly suggested you have some non-declaration statements at the class level which is not valid.

Your code from the script task have some issues with the closing brace --

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;

        }

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