Sunday, August 22, 2010

Data from Dynamic Sheets of an Excel

This is with reference to a Question asked on the blog:
How to get data from different sheets in an Excel WorkBook  having same column names?

Basic idea to get the done is get name of all the sheets in an variable and then iterate over that variable and get data from respective Sheet.

To Demonstrate I have created an Excel sheet having 3 sheets having only one column "ColumnName"
1. Excel WorkBook

2. Now lets create the package with below variables

3. Variable QueryStmt will hold a dynamic value which would be used as SQL Command passed to Excel.
Check EvaluateAsExpression Property as True and set Expression as

4a. Next Step is to use Script Task to get all the sheet names in Variable SheetNamesList.

4b. Write below code in Script Task to get all the sheets names in SheetNamesList Variable.
Directive needed using System.Data.OleDb;
        public void Main()


            OleDbConnection con = null;

            System.Data.DataTable dt = null;

            string ExlPath = Dts.Variables["ExcelPath"].Value.ToString();


            String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source=" + ExlPath + ";Extended Properties=Excel 8.0;";

            con = new OleDbConnection(conStr);


            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


            String[] excelSheetNames = new String[dt.Rows.Count];

            int i = 0;

            foreach (DataRow row in dt.Rows)


                excelSheetNames[i] = row["TABLE_NAME"].ToString();



            Dts.Variables["SheetNameList"].Value = excelSheetNames;

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
5  Create a For Each Loop to iterate over SheetNameList

b. Set the index to SheetName to get counter sheet name

6. Add a DATA FLOW TASK and use Excel Source to connect to the WorkBook and Set the properties as

7. Thats it.. Execute the package and get the data...



Anonymous said...

Hi Rahul,

In the Expression builder which prop we need to override..
can you make it clear..........


Rahul Kumar said...

Roshan .. which step are u referring to?

Anonymous said...

Rahul....Theird Step


Rahul Kumar said...

its an Expression for QueryStmt variable...
u dont have to override any property there.. Variable expression just have one default (value) property.. but remember to set EvaluateAsExpression property also as true.

Anonymous said...

Thanks Rahul....

Your work is really Appreciated.


Rahul Kumar said...


Anonymous said...

hi rahul could you please post an example how to extract data by looping over different excel files and flat files onto a sql server destination

Post a Comment