Problem Statement:

In a company say ABC Inc. you have stored data in the database for each department. Data includes Location, total salary, # of Paid vacation, and total #of employees for each department. We have a department head for every department and once in every month we need to send these department head's a summary report reflecting this data in an excel file. So our goal is to create different excel reports for each department.

Tools Required

SQLServer2008, SSIS

Solutions:

1. We will create a simple table in SQL Server as below:

create table DepartmentDetails

(Department varchar(100) not null

,Location varchar(100) not null

,TotalSalary money not null

,PaidVacation decimal(15,2) not null

,EmployeeCount decimal(15,2) not null

)

1. Let's Populate our table

    

insert into DepartmentDetails VALUES

('IT','Lexington', $125000.00,5,100 ),

('Legal', 'Lexington', $125000,5,100 ),

('Sales', 'Lexington', $125000,5,100 ),

('Operations','Lexington', $125000,5,100 ),

('HumanResource','Lexington', $125000,5,100 ),

('Supplies','Lexington', $125000,5,100 ),

('IT','Wyoming', $5000.00,5,100 ),

('Legal', 'Wyoming', $5000,5,100 ),

('Sales', 'Wyoming', $5000,5,100 ),

('Operations','Wyoming', $5000,5,5 ),

('HumanResource','Wyoming', $5000,5,1 ),

('Supplies','Wyoming', $5000,5,100 ),

('IT','San Francisco', $500000.00,5,200 ),

('Legal', 'Wyoming', $500000,5,100 ),

('Sales', 'Wyoming', $500000,5,100 ),

('Operations','Wyoming', $500000,5,50 ),

('HumanResource','Wyoming', $500000,5,1 ),

('Supplies','Wyoming', $500000,5,10 );

GO

1. Now we create a procedure to retrieve data from the table

   create Procedure dbo.getDepartmentDetail

   (

   @Department varchar(50)

   )

   AS

   BEGIN

   select Department, Location, TotalSalary, PaidVacation, EmployeeCount

   from DepartmentDetails where Department=@Department

END

1. We now fire up our BIDS and create a new project and then create a new package named "SplitExcel"
2. First thing we will do is create some variables as shown below

1. Now we make an OLEDB connection under connection manager, so right click under ConnectionManager tab, select New OLE DB Connection and either create the connection to Database where above tables and procedures are created or if the connection to same Database is already available then select fom the list.
2. We will rename our OLE DB connection manager to DepartmentDatabase, so right click over newly created connection manager and select rename.
3. Now we take an Execute SQL task from toolbox and rename it to "GetDistinctDepartment" and configure as shown below

    

   

    

4. We also need to make sure that ResultSet is configure as below. So add a new parameter assign the variable name.

1. Now we bring ForEachLoopContainer from tool box and connect it with GetDistinctDepartment and start configuring it as below. Right click on ForEachLoopContainer and click EDIT

    

   

    

   And Variable Mapping as show below

    

   

    

2. Create an Excel Connection Manager under Connection Managers as below:

Excel Connection Manager that is created above we can rename it to ExcelCon

1. Now we drag Execute SQL task inside ForEachLoopContainer and configure as below

    

   

    

    

    

   

    

    

2. Now we drag a Data Flow Task inside ForEachLoopContainer and connect ExecuteSQL to Data Flow Task and start configuring Data Flow
3. DataFlow would look like below, since we have not configure DataFlowSource and destination there is a small cross sign.

    

   

    

4. Configure OLE DB Source Editor. Click on Edit and follow below

1. Click on parameters and configure as below:

1. All the text datatypes should be converted to unicodes, totalsalary will be curreny type and PaidVacation and EmployeCount to numeric as shown below:

1. Now we need to configure Excel Destination as below: under Data Access Mode
2. Click on New as highlighted below

Use below script to create the table

CREATE TABLE `DepartmentReports` (

`Department` LongText,

`Location` LongText,

`OLE DB Source.TotalSalary` Currency,

`OLE DB Source.PaidVacation` Decimal(15,2),

`OLE DB Source.EmployeeCount` Decimal(15,2)

)

1. Once you click OK below window will pop up, click OK

1. Choose the report from drop down.

1. Under Mapping , map the column names with datatypes from data conversion and click OK

1. Execute the package and everything should turn green

Under the path that we used , there will be different excel files created for each department