SSIS – Reading Excel Sheet With Different Meta Data and Store it in Multiple Table

Introduction

Again I am with my friend request. In this article I am trying to demonstrate a real life scenario that my friend asks me. Hope he will be happy to get that.
I think this article will be interesting and put some value for SSIS developers.

The Case Scenario

I have a Excel file in the name of StudentDetails.xlsx which contains two sheet Sheet-1 have information about Student Roll and Student Name. The Sheet-2 has Student Roll, Marks1, Marks2 and Marks3.

As we can see that the Excel has different meta data in different Sheet. We want to store the

Sheet1 à tbl_StudentDetails

Sheet2 à tbl_StudentMarks

Hope it will make some séance now and you will enjoy the session

The sample Excel Sheet is bellow

How we Find the Solution of It

The Control Flow Diagram

The Data Flow Diagram

Step -1 [ Execute SQL Task ]

Just truncate both the table in Destination

TRUNCATE TABLE [dbo].[tbl_StudentDetails];

TRUNCATE TABLE [dbo].[tbl_StudentMarks];

Step – 2 [ Make the Connection Manager for Excel ]

Provider:

.Net Provider for OledDb\Microsoft Office 12.0 Access Database Engine OEL DB Provider

Extended Propertied:

Excel 12.0;HDR=YES

Step – 3 [ For Each Loop Container ]

Here the Variable Mapping is very important. As the Excel provide the sheet as Table, so we can see in the Schema Restriction interface the TABLE_NAME comes under array collection 2. So we provide the Index of the variable as 2.

Step – 4 [ The Expression Task ]

We are just use the Variable (@[User::v_ExcelSheetName] – String Type and Scope Package Level) over here so that we can use the precedence constraint with the next level of Task.

Step – 5 [ The Precedence Constraint ]

Depends on Precedence Constraint (If the Sheet-1 or Sheet-2) the flow goes on different Data Flow Task.

Step – 6 [ Data Flow Task ]

Here I am not going to describe it as you all know very well about it. But here we have to change one settings of Data Flow task property Expression. We also make the Delay Validation of data flow task as TRUE.

We must provide the value of property named

[ADO NET Source].[TableOrViewName]=@[User::v_ExcelSheetName]

As the Excel Sheet name cames in the variable that is mentioned above.

Step – 7 [ Running the Package ]

Step – 8 [ Observation After Running the Package ]

SELECT * FROM [dbo].[tbl_StudentDetails];

RollNumber       StudentName

1                              Joydeep Das

2                              Deepasree Das

3                              Shipra Das

4                              Sonal Mishra

5                              Tina Guha

SELECT * FROM [dbo].[tbl_StudentMarks];

RollNumber       Maks1   Marks2 Marks3

1                              50           40           80

2                              60           60           90

3                              50           20           60

4                              20           10           40

5                              80           90           70

Special Note :

Sometimes the Error comes when you run the package like this

Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate.

To fix it go the Project Propertied à Debugging Tab à Run64BitRuntime to False

Hope you like it.

Posted by: JOYDEEP DAS