I had to import excel data (just one sheet) into SQL database. The excel spreadsheet had multiple sheets where I am interested in only one sheet.
a) Create a new SSIS variable named "SheetName"
b) Create a script Task just before the Excel Source Task.
c) Add the following code to the main method of the script task
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::ExcelFile"].Value.ToString();
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
excelConnection = new OleDbConnection(connectionString);
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;
Dts.Variables["User::SheetName"].Value = excelTables;
Dts.TaskResult = (int)ScriptResults.Success;
d) In your Excel source select the "Data access mode" as "Table name or view name variable".
e) Select the "User::SheetName" variable from the "Variable Name" dropdown list.
Implemented your resolution and in updating the variable name I get desination table name has not been provided.
Send me more information to help you out.
I like your solution and I am also trying to do something like this. Basically what we are trying to doing is we want to loop around excel work sheets in excel file and check if we have values like "Country" , "Address" and "State". if these values are populated than I would like to store "SheetName" into variable and use that for further processing . Can you please assist me with that. Thanks in advance
I have a quick question for you. If I know the sheet name but it has the name tagged on to the end, do I need to do a Script task (SSIS 2008)?
If the sheetname is constant and doesn't change with the spreadsheet then there is no need to use script task. If the sheet name is dynamic with date or timestamp embedded with the filename then you need to use script task.
What is the namespace for "OleDbConnection"?
Use System.Data.OleDb namespace
Hey, this was really helpful, thank you!
Post a Comment