Clash Royale CLAN TAG#URR8PPP

Import EXCEL with SSIS without knowing sheename

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

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'

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


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

[User:SheetName]

Script task

can anyone please check what is missing?

1 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:CESLtdELKAYReportsWork2Book1.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;

}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.