Microsoft Excel spreadsheet is use by many individuals and organization, business to store data and perform calculation on this data. Reading and Writing data into Ms-Excel using SSIS is very common. Ms-Excel spreadsheet can store data into multiple sheets. Reading data from these sheets and storing it into a SQL table is pretty easy with SSIS. In this post we will learn to read data from multiple sheets and store it into a SQL table.
For demonstration purpose let us consider following Ms-Excel spreadsheet with data in three different sheets. Each sheet contains two colum data - DeptCode and Deptname.
Let us create a SQL table which will store the data from all three sheets of Ms-Excel.
CREATE TABLE [dbo].[tblDepartment]( [DeptCode] [varchar](50) NOT NULL, [DeptName] [varchar](50) NOT NULL ) ON [PRIMARY]
Let us create the SSIS package now. The very first thing we did is to create a variable with the name “SheetName”. The default value in this variable is name of first sheet i.e. Sheet1$.
In the control flow tab we have added two tasks – one Foreach Loop Container and one Data Flow Task.
Foreach Loop Container will loop through each of sheet and perform Data Flow Task activities. On Foreach Loop container editor dialog box we have selected Foreach ADO.NET Schema Rowset Enumerator and in Connection we have selected New Connection.
From the Connection Manager list we have selected Microsoft Jet 4.0 OLE DB Provider.
We have selected the Ms-Excel spreadsheet as Database file name
From the All properties tab we have setup Extended Properties value to Excel 8.0
From the Schema list we have selected Tables.
From the Variable Mappings tab we have setup the variable mapping with user defined variable "SheetName" to index 2.
On Data Flow Tab we have following three controls.
Excel Source controls read the data from each of the sheet. On Excel Source we have setup the Data access mode to Table name or view name variable. We have selected the variable name as SheetName, we have defined this variable earlier.
From the Columns we have selected the two output columns – Deptcode and DeptName.
Data Conversion control convert the data type from Unicode to Varchar as the data we read from Excel is treated as Unicode.
OLE DB Destination controls load the data into SQL table. We have mapped the derived columns with SQL table.
After setting up the SSIS package, I run the SSIS pacage and it runs successfully,
I checked the SQL table and all data from all the sheets were loaded successfully.
