Saturday 12 December 2015

SSIS – Where Destination is Excel

Introduction
Another simple scenario came from my friend where the destination is Excel. SSIS is a wonderful tool and we have to think how to use it. The Design plan is important over here. If we have a good understanding of each Components or Task in SSIS we definitely solve all by providing a few minutes to make a Good design plan. Here in this article the Scenario is Simple but need a good design plan to execute it. Hope it will be helpful and you enjoy the Session.

What the Scenario is
Actually the Scenario is quite simple.
We have a Database Table named [dbo].[tbl_Employee]

EmpId
EmpName
EmpGrade
EmpDepartment
1
Joydeep Das
A
DBA
2
Sukamal Jana
B
DBA
3
Subrata Kar
C
DBA
4
Avijit Gurui
A
Project Manager
5
Subdip Das
B
Project Manager
6
Arabinda Sarkar
A
Development
7
Santi Nath
C
Development
8
Indrajit Sarkar
B
Development
                                   
We have to segregate the data depends on Employee Department and Store it into a pre formatted Excel Work book Different Sheet.
The Sample of the Excel Work book is provided bellow



Hope you understand the Scenario.

How We Solve it

Step – 1 [ Create the Base Table and Insert some Records in it ]

IF OBJECT_ID(N'[dbo].[tbl_Employee]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_Employee];
   END
GO
CREATE TABLE [dbo].[tbl_Employee]
  (
    EmpId               INT         NOT NULL IDENTITY PRIMARY KEY,
      EmpName           VARCHAR(50) NOT NULL,
      EmpGrade          CHAR(1)     NOT NULL,
      EmpDepartment     VARCHAR(50) NOT NULL
  );
GO

INSERT INTO [dbo].[tbl_Employee]
   (EmpName, EmpGrade, EmpDepartment)
VALUES  ('Joydeep Das', 'A', 'DBA'),
        ('Sukamal Jana', 'B', 'DBA'),
        ('Subrata Kar', 'C', 'DBA'),
        ('Avijit Gurui', 'A', 'Project Manager'),
        ('Subdip Das', 'B', 'Project Manager'),
        ('Arabinda Sarkar', 'A', 'Development'),
        ('Santi Nath', 'C', 'Development'),
        ('Indrajit Sarkar', 'B', 'Development');
GO

SELECT * FROM [dbo].[tbl_Employee];

EmpId   EmpName           EmpGrade            EmpDepartment
1              Joydeep Das        A                             DBA
2              Sukamal Jana      B                             DBA
3              Subrata Kar         C                             DBA
4              Avijit Gurui         A                             Project Manager
5              Subdip Das          B                             Project Manager
6              Arabinda Sarkar  A                             Development
7              Santi Nath           C                             Development
8              Indrajit Sarkar     B                             Development

Step – 2 [ The Data Flow Task of the Package ]



Step – 3 [ The OLEDB Source ]



Step – 4 [ The Data Conversion Task ]



Step – 5 [ The Conditional Split Task ]




Case
Condition
DBA_DataFlow
EmpDepartment == "DBA"
PM_DataFlow
EmpDepartment == "Project Manager"
DEV_DataFlow
EmpDepartment == "Development"



Step – 6 [ Excel Destination ]



Step – 7 [ Run the Package and Observe ]






Hope you like it.





Posted by: MR. JOYDEEP DAS

3 comments:

  1. that is great and mark able. how to make it dynamic

    ReplyDelete
    Replies
    1. Ok... The Dynamic we have to Use some Scripting via Script Task. Easy one.

      Delete
  2. Hello @Rohit
    Please go to the Article
    http://www.sqlknowledgebank.blogspot.in/2015/12/ssis-dynamic-excel-sheet-choosing-in.html

    ReplyDelete