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