Introduction
This is a
request came from of my friend at southern part of India. It is a little hazy scenario
that we must understand. The main portion is that package is a use of preformatted
Excel templates to populate data as output and a stored procedure that generate
the output. Here I am not providing the exact scenario case as it is belongs to
someone. We just recreate the case with a simple example of Student and Marks
relation.
Hope it will
be informative.
The Case Scenario
We have a
table named tbl_StudentDetails which contains
|
StdRoll
|
StdName
|
|
1
|
Joydeep Das
|
|
2
|
Deepasree Das
|
|
3
|
Shipra Roy Chowdhury
|
The table tbl_StudentMarks which contains
|
IdSrl
|
StdRoll
|
Subjects
|
Marks
|
|
1
|
1
|
English
|
60
|
|
2
|
1
|
Math
|
70
|
|
3
|
1
|
Bengali
|
90
|
|
4
|
2
|
English
|
90
|
|
5
|
2
|
Math
|
50
|
|
6
|
2
|
Bengali
|
40
|
|
7
|
3
|
English
|
90
|
|
8
|
3
|
Math
|
90
|
|
9
|
3
|
Bengali
|
30
|
We have a
Stored Procedure named proc_GetStudentRecords
. It contains a parameter named @p_OutputType.
If we supplied the value “A” for these parameters we get the average value and
if we supplied “S” in these parameters we get the Sum of Marks value.
The output
must be displayed in a pre formatted Excel file. The Excel file is located in
the folder named Templates. Before generating
output it just copy the excel file from
Templates folder to Output folder and populate the value.
Hope you
understand the scenario.
How We DO That
Step
– 1 [ The Source Table with Data ]
IF OBJECT_ID(N'[dbo].[tbl_StudentDetails]', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_StudentDetails];
END
GO
CREATE TABLE [dbo].[tbl_StudentDetails]
(
StdRoll INT NOT NULL IDENTITY PRIMARY KEY,
StdName VARCHAR(50) NOT NULL
);
GO
INSERT INTO [dbo].[tbl_StudentDetails]
(StdName)
VALUES('Joydeep Das'),
('Deepasree Das'),
('Shipra Roy Chowdhury');
GO
IF OBJECT_ID(N'[dbo].[tbl_StudentMarks]', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_StudentMarks];
END
GO
CREATE TABLE [dbo].[tbl_StudentMarks]
(
idSrl INT NOT NULL IDENTITY PRIMARY KEY,
StdRoll INT NOT NULL,
Subjects VARCHAR(50) NOT NULL,
Marks INT
);
GO
INSERT INTO [dbo].[tbl_StudentMarks]
(StdRoll, Subjects,
Marks)
VALUES(1, 'English', 60),
(1, 'Math', 70),
(1, 'Bengali', 90),
(2, 'English', 90),
(2, 'Math', 50),
(2, 'Bengali',40),
(3, 'English', 90),
(3, 'Math', 90),
(3, 'Bengali', 30);
Step
– 2 [ The Stored Procedure That Retrieved the Records ]
IF OBJECT_ID(N'[dbo].[proc_GetStudentRecords]', N'P')IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[proc_GetStudentRecords];
END
GO
CREATE PROCEDURE [dbo].[proc_GetStudentRecords]
(
@p_OutputType CHAR(1)
)
AS
BEGIN
IF @p_OutputType = 'A'
BEGIN
SELECT a.StdRoll, a.StdName, AVG(b.Marks) AS Marks
FROM [dbo].[tbl_StudentDetails]
AS a
INNER JOIN [dbo].[tbl_StudentMarks] AS b
ON a.StdRoll = b.StdRoll
GROUP BY a.StdRoll, a.StdName;
END
IF @p_OutputType = 'S'
BEGIN
SELECT a.StdRoll, a.StdName, SUM(b.Marks) AS Marks
FROM [dbo].[tbl_StudentDetails]
AS a
INNER JOIN [dbo].[tbl_StudentMarks] AS b
ON a.StdRoll = b.StdRoll
GROUP BY a.StdRoll, a.StdName;
END
END
GO
Step
– 3 [ Preformatted Excel Sheet ]
Step
– 4 [ SSIS Control Flow / Data Flow Task ]
Step
– 5 [ The File System Task ]
For source
and destination of Excel file we create two variables.
|
Variable name
|
Values
|
|
v_Destinaion
|
E:\Preparation\SSIS\FlatFile\Output\StudentGrade.xlsx
|
|
v_Source
|
E:\Preparation\SSIS\FlatFile\Templates\StudentGrade.xlsx
|
Step
– 6 [ Data Flow Tasks – OELDB Source ]
Variable used
for Stored Procedure Parameters
|
Variable Name
|
Value
|
|
v_SPParamValue
|
A
|
Step
– 7 [ Data Flow Task – Data Conversion ]
Just Convert
Student name from DT_STR to
DT_WST
Step
– 8 [ Excel Destination ]
Step
– 9 [ Observation ]
Hope you like
it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment