Sunday 13 December 2015

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

1 comment:

  1. there is some issue in the excel mapping it can not map if it dynamically variable

    ReplyDelete