hnhnews

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.