Extracting Data from Multiple Excel sheet using Multiple Tags

Follow the steps given here to know exactly how to extract data from various Excel sheets using multiple tags.

Step 1 Create few samples with Excel with DimCustomer or DimEmployee from Adventures Works Database.

Step 2: Open the BIDS with SSIS Project and add an empty Package. Drag and drop the Foreach loop Container and name it as Excel File Reader

Step 3: Create 3 Variables as varDirectory, varExtension and varExcelPath for passing the values dynamically to read the file name and sheet name from the respective Folders.

Step 4: Add these Varibles into the Excel File Reader — Foreach loop. Set the Enumerator to File Enumerator and click on the Expression below the Enumerator Setting in Collection tab as shown

Step 5: Click on the Variable Mappings tab, and set the output of Foreach loop to a Variable varExcelPath. varExcelPath is the combination of varDirectory and varExtension — > varExcelPath = varDirectory + varExtension. E.g. varExcelPath = E:\EnglishOccupation\Manual.xls will be created dynamically. But for designing purpose, few data has to be placed in the Variables.

Step 6: Drag and Drop the next Foreach loop and set the Enumerator as Foreach ADO.NET Schema Rowset Enumerator

Step 7: Create a ADO.net Connection called Excelschema with respective data as shown below. Select .NET Providers for OLEDB\ Microsoft Office 12.0 Access Database Engine OLEDB Provider. Add the Excel sheet path to the Server or Filename.

Step 8: Click “All” button of the Same Connection Manager and enter Excel 12.0 in Extended Properties

Step 9: Click on Excelschema connection to see the Properties and click on the Expression, added the Variable @varExcelPath to the ServerName.

Step 10: Again click on the Inner Foreach loop to add the Connection string and the Output configuration

Step 11: click on the Variable Mapping and use the Variable varSheet with the Data sheet name Management – A$ created earlier and use the Variable in Variable Mapping by setting the Index as 2

Step 12: Add the Data Flow Task with Excel Source, Derived Column and OLEDB Source. Excel source is used to read the Data from Excel sheets and Derived Column is used to find the Sheet Name and Excel Name

Step 13: Now run the package and check the Database. Create a table as shown below in the SQL Server and assign the table to OLEDB Destination. Click on the Excel Connection and add the variable @varExcelPath

CREATE TABLE [dbo].[CustomerData]
[CustomerKey] [nvarchar](255) NULL,
[FirstName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[Gender] [nvarchar](255) NULL,
[EmailAddress] [nvarchar](255) NULL,
[YearlyIncome] [nvarchar](255) NULL,
[EnglishEducation] [nvarchar](255) NULL,
[EnglishOccupation] [nvarchar](255) NULL,
[FilePath] [nvarchar](4000) NULL,
[SheetName] [nvarchar](4000) NULL

Step 14: Now run the package and check the Database table.

We hope you find this article useful. We are a Microsoft gold certified partner providing offshore software development on Microsoft technologies (.NET, SQL), custom application development, SharePoint, Microsoft Business Intelligence, Dynamics ERP & CRM and Mobile application development on iOS, Windows and Android platforms.

Please do contact us if you have any questions pertaining to this article or any other queries that you might have relating to our services.

Tags: Microsoft BI
previous post: Installing Master Data Services next post: Multi targeting using portable assemblies