SSIS – Dynamic Excel Sheet Choosing in Destination

Introduction
The Article is the continuation of one of my previous article named 

“SSIS – Where Destination is Excel”

Web Ref:

 http://sqlknowledgebank.blogspot.in/2015/12/ssis-where-destination-is-excel.html

To continue this article you must read the above article as the Scenario is the same only we have to make it dynamically.

If you go to the previous article you can find that we are taking three Excel Destination for storing data in there different Sheet of same Excel Work book. After publishing this article one of my friends say in comments that “Can We Make Dynamically”. Here is the solution for that.

Here it is important to remember that we are not creating any Excel Sheet but we used the pre formatted excel sheet in the work book.

Hope it will be interesting.

 

What the Scenario is

The Scenario is same as the previous article scenario named “SSIS – Where Destination is Excel”. You can find it in
http://sqlknowledgebank.blogspot.in/2015/12/ssis-where-destination-is-excel.html

So I am not going to re-type the scenario again.

 

How we Solve it

Step – 1 [ The Base Table and Insert Some Records in it ]

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

Step – 2 [ The Control Flow Tab ]

Step – 3 [ The  Variable List  ]

Variable Name	Data Type
v_DepartmentName	String
v_ExcelSheetName	String
v_ObjDepartment	Object

Step – 4 [ The  Execute SQL Task ]

Here we find the distinct Department name by which we ebstruct data from table and find the Excel Sheet name.

SQL Statement

SELECT DISTINCT EmpDepartment FROM [dbo].[tbl_Employee];

Step – 5 [ The  ForEach Loop Container ]

Step – 6 [ The  Expression Task  ]

Here we are trying to populate the Excel Sheet name

Expression

@[User::v_ExcelSheetName]= @[User::v_DepartmentName]+"$"

Step – 7 [ The  Data Flow Task  ]

OLEDB Source

SQL Command

SELECT EmpId, EmpName, EmpGrade

FROM   [dbo].[tbl_Employee]

WHERE  EmpDepartment =?

Step – 8 [ The  Data Conversion  ]

Here we Just convert the data type of EmpName and EmpGrade from DT_STR to DT_WSTR

Step – 8 [ The  Excel Destination  ]

It is very important. Where we are dynamically change the Excel Sheet name.

Hope you like it.

Posted by: MR. JOYDEEP DAS