Import EXCEL with SSIS without knowing sheename

Question

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?

Answer 1

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;

        }