Friday, January 07, 2011

Dynamic Sheet Name in SSIS Excel Spreadsheet imports

Scenario

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.

Solution

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

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

Dts.Variables["User::SheetName"].Value = excelTables[0];

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.

8 comments:

Unknown said...

Implemented your resolution and in updating the variable name I get desination table name has not been provided.

Anupama Natarajan said...

Send me more information to help you out.

Investor said...

Hello,
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

ConsultJack said...

Hi!
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)?

Anupama Natarajan said...

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.

Unknown said...

What is the namespace for "OleDbConnection"?

Anupama Natarajan said...

Use System.Data.OleDb namespace

Unknown said...

Hey, this was really helpful, thank you!

Post a Comment