Data from Dynamic Sheets of an Excel

This is with reference to a Question asked on the blog:

Question:

How to get data from different sheets in an Excel WorkBook  having same column names?

Solutions:

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

            con.Open();

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

                i++;

            }



            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

a.

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...

Thanks!!