Wednesday 18 November 2015

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

11 comments:

  1. Nice article ,Thanks for sharing your knowledge with us..

    ReplyDelete
  2. this is best one that i have seen ever. keep going.

    ReplyDelete
  3. there is one more scenario. it may be possible that excel file is dynamic it can have n number of sheet and have n number of tables

    ReplyDelete
    Replies
    1. Thanks @ Rohit...
      I will provide the solution in my coming Article ...

      Delete
  4. i downloaded the cs go no steam warzone but when i try to connect it to any server it shows some screen and instantly rolls back to the main screen..
    load balancing server

    ReplyDelete
  5. I am really very agree with your qualities it is very helpful for look like home. Thanks so much for info and keep it up.
    pages software for windows

    ReplyDelete
  6. I visited your blog for the first time and just been your fan. I Will be back often to check up on new stuff you post!
    Fast VPN Europe

    ReplyDelete
  7. Post is very informative,It helped me with great information so I really believe you will do much better in the future.
    Public Teamspeak Server

    ReplyDelete
  8. Thanks for sharing info. Keep up the good work...We hope you will visit our blog often as we discuss topics of interest to you
    خريد وی پی ان

    ReplyDelete