SSIS Demo Project

Demo Project : SSIS Logging

Submitted by
Harshith V
Trainee Software Engineer
Custom Solution Services
Excelsoft Technologies Inc
SSIS Demo Project

Introduction:

SSIS Stands for SQL SERVER INTEGRATION SERVICES

SSIS is a fast flexible data warehousing tool used for data extraction,
transformation, and data loading.

It makes it easy to load the data from one database to another database
such as SQL Server, Oracle, Excel file, etc.

It is a data warehousing tool used for data extraction, loading the data into
another database, transformations such as cleaning, aggregating, merging
data, etc.

SSIS tool also contains the graphical tools and window wizards workflow
functions such as sending email messages, ftp operations, data sources.

SSIS is used to perform a wide range of transformation and integration

tasks. As a whole, the SSIS tool is used in data migration.

Some key features and uses of SSIS include:

ETL (Extract, Transform, Load): SSIS is commonly used for ETL processes,
where data is extracted from multiple sources, transformed according to
business rules or requirements, and loaded into a destination database or
data warehouse.

Data Migration: SSIS can be used to migrate data from one database to
another, whether it's within the same server or across different servers.

1
SSIS Demo Project

Data Warehousing: SSIS plays a crucial role in building and populating data
warehouses. It can efficiently handle the extraction of data from various
sources, transforming it to fit the data warehouse schema, and loading it
into the warehouse.

Workflow Automation: SSIS provides a visual interface for designing

workflows, making it easy to automate various data-related tasks such as
file operations, FTP transfers, email notifications, and more.

Error Handling and Logging: SSIS includes robust error handling

capabilities, allowing developers to define how errors are handled during
package execution. It also provides logging features to capture execution
details for troubleshooting and auditing purposes.

Integration with other Microsoft Tools: SSIS integrates seamlessly with

other Microsoft products such as SQL Server Management Studio (SSMS),
SQL Server Data Tools (SSDT), and SQL Server Reporting Services (SSRS),
allowing for a comprehensive data management and reporting solution.

2
SSIS Demo Project

Project Description

Create a SSIS Package that reads the data from any flat file source and load
the data into the OLE DB Destination based on the below conditions:
1. The data will be of student data with qualifications
2. The Qualifications listed in flat file are like
BCA,BCOM,BSC,MCA,MSC,MCOM,PHD
3. All the Under graduate student’s data must be maintained in one UG
table, likewise Post graduates in PG table and Ph.d in Professional
Courses table.
4. Maintain Log of the SSIS Package in another table called Execution_log
5. Execution_Log contains

JobId JobName StartTime EndTime Duration Status Message

Identity(1,1) Operation Starttime Endtime Total- Success Message
name Duration or
Failure

Assigned by : Chella Ganesh

3
SSIS Demo Project

Solution

Flat File Source:

I selected CSV as a source and CSV contains all the data related to the
student.

4
SSIS Demo Project

In SSIS First we need to select Data Flow Task in order to select the data
from the flat file source and configuration of flat file source and data split.

In Dataflow task, the below diagram represents the design of the data flow
task,

Overview of DataFlow Task:

This Dataflow design diagram

Flat File manager: It is used to configure the flat file and map the columns
inside the CSV file.

5
SSIS Demo Project

6
SSIS Demo Project

Conditional Split Transformation Editor:

Conditional Split transformation Editor is used to code the conditions that

are used to split the data inside the CSV file.

Here Condition is given based on the qualification i.e if the first letter in
the qualification field is B means UG, M means PG, P Means
Proffessional_courses table in SQL Server.

7
SSIS Demo Project

OLE DB Destination Editor:

OLE DB Destination Editor means it is used to configure the Connection of

the OLE DB Destination in which the data is stored after the conditional
split.

8
SSIS Demo Project

Mappings of the columns between result column and OLE DB Destination

Table’s Column.

9
SSIS Demo Project

Again in Execute SQL TASK 1

These are the Configurations done In the below diagram Execute SQL Task 1 editor.
In SQL Statement field
USE SSIS
EXEC LogSSISExecution @NameOfTheJob =?

is given, and this task will execute the LogSSISExecution Procedure

10
SSIS Demo Project

variables are mapped in parameter mapping in Execute SQL Task 1 editor.

11
SSIS Demo Project

In Execute SQL Task Editor I have SQLStatement USE SSIS EXEC ExecutionLog
Note: All the sp’s that are used in this package is entered below.

12
SSIS Demo Project

After all these configurations, SSIS Package ran successfully

The result set After successful execution of SSIS Package.

13
SSIS Demo Project

LOG Table

14
SSIS Demo Project

Stored Procedures:

1. Dbo.ExecutionLog: The SP that is used in Execute SQL Task

USE [SSIS]
GO

/****** Object: StoredProcedure [dbo].[ExecutionLog] Script Date: 20-03-2024 11:03:47

******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ExecutionLog]

AS
BEGIN
DECLARE @JOBNAME NVARCHAR(100);
DECLARE @STARTTIME DATETIME;
DECLARE @ENDTIME DATETIME;
DECLARE @DURATIONINSECONDS INT;
DECLARE @STATUS BIT;
DECLARE @MESSAGE NVARCHAR(MAX);

-- Check if there is a Data Flow Task event in the sysssislog table

IF EXISTS (SELECT TOP 1 EVENT FROM sysssislog WHERE EVENT = 'OnPipelinePostEndOfRowset'
ORDER BY EVENT DESC)
BEGIN
SET @JOBNAME = 'INSERTION';

SELECT TOP 1 @MESSAGE = MESSAGE

FROM sysssislog
WHERE EVENT = 'OnPipelinePostEndOfRowset'
ORDER BY message DESC;
END

IF EXISTS (SELECT EVENT FROM sysssislog WHERE EVENT = 'OnError')

SET @JOBNAME = 'ERROR';

-- Retrieve StartTime and EndTime of the package

SELECT TOP 1 @STARTTIME = StartTime
FROM sysssislog
WHERE EVENT = 'PackageStart' AND SOURCE='Package'
ORDER BY StartTime DESC;

SELECT TOP 1 @ENDTIME = EndTime

FROM sysssislog
WHERE EVENT = 'PackageEnd' AND SOURCE='Package' AND datacode=1
ORDER BY EndTime DESC;

-- Calculate duration in seconds

SET @DURATIONINSECONDS = DATEDIFF(SECOND, @STARTTIME, @ENDTIME);

-- Set status based on EndTime

IF @ENDTIME IS NOT NULL AND @ENDTIME >= @STARTTIME

15
SSIS Demo Project

BEGIN
SET @STATUS = 1; -- Successfully completed
-- SET @MESSAGE='ALL ROWS INSERTED'
END
ELSE
BEGIN
SET @STATUS = 0; -- Not successfully completed

-- Retrieve error message if any

SELECT TOP 1 @MESSAGE = MESSAGE
FROM sysssislog
WHERE EVENT = 'OnError'
ORDER BY StartTime DESC;
END
-- Insert into SSISExecutionLog table
INSERT INTO SSISExecutionLog (NameOfTheJob, StartTime, EndTime, DurationInSeconds, Status,
Message)
VALUES (@JOBNAME, @STARTTIME, @ENDTIME, @DURATIONINSECONDS, @STATUS, @MESSAGE);
END;
GO

The Detailed Explaination of SP

The above stored procedure, ExecutionLog, is designed to log SSIS package execution
details into an SSISExecutionLog table based on specific conditions derived from the
sysssislog system table.

Below is a detailed description of the stored procedure and an overview of a SSIS

project that might utilize it, focusing on conditional splitting and logging.

Stored Procedure Overview:

Parameters: None

Variables:

@JOBNAME: NVARCHAR(100) variable to store the name of the job.

@STARTTIME: DATETIME variable to store the start time of the job.
@ENDTIME: DATETIME variable to store the end time of the job.
@DurationInSeconds: INT variable to store the duration of the job in seconds.
@Status: BIT variable to indicate the status of the job (success or failure).
@MESSAGE: NVARCHAR(MAX) variable to store error messages.

16
SSIS Demo Project

Logic:

 The procedure checks if specific events ('OnPipelinePostEndOfRowset' and

'OnError') exist in the sysssislog table.
 Based on the presence of these events, it sets the @JOBNAME variable to
'INSERTION' or 'ERROR LOG'.
 If 'OnError' event exists, it retrieves the top 1 error message and sets it to the
@MESSAGE variable.
 It then retrieves the start time and end time of the package execution from the
sysssislog table.
 Calculates the duration of the execution in seconds.
 Determines the status of the execution based on the start and end times.
 Inserts the job details (name, start time, end time, duration, status) into the
SSISExecutionLog table.

17
SSIS Demo Project

2. Dbo.ExecutionLog: The SP that is used in Execute SQL Task1

USE [SSIS]
GO

/****** Object: StoredProcedure [dbo].[LogSSISExecution] Script Date: 20-03-2024 11:10:25

******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LogSSISExecution]

(
@NameOfTheJob NVARCHAR(100)
)
AS
BEGIN
DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;
DECLARE @DurationInSeconds INT;
DECLARE @Status BIT;
DECLARE @Message Nvarchar(max);
SET @StartTime = CONVERT(DATETIME, GETDATE());

SELECT @StartTime AS StartTime;

--Excecuting two sp’s that give us starttime and endtime

EXEC dbo.SSISExecutionStartTime @StartTIME = @StartTime OUTPUT;

EXEC SSISExecutionStopTime @ENDTIME = @EndTime OUTPUT;

SET @DurationInSeconds = DATEDIFF(NANOSECOND, @StartTime, @EndTime);

IF @EndTime IS NOT NULL AND @EndTime >= @StartTime

BEGIN
SET @Status = 1; -- Successfully completed
SET @Message='ALL ROWS INSERTED'
END
ELSE
BEGIN
SET @Status = 0; -- Not successfully completed
SET @Message='ERROR'
END
INSERT INTO SSISExecutionLog (NameOfTheJob, StartTime, EndTime, DurationInSeconds,
Status,Message)
VALUES (@NameOfTheJob, @StartTime, @EndTime, @DurationInSeconds, @Status,@Message);
END;
GO

18
SSIS Demo Project

SSIS Starttime sp
USE [SSIS]
GO

/****** Object: StoredProcedure [dbo].[SSISExecutionStartTime] Script Date: 20-03-2024

11:04:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSISExecutionStartTime]

(
@StartTIME DATETIME OUTPUT
)
AS
BEGIN

--DECLARE @EndTime DATETIME;

SET @StartTime = CONVERT(DATETIME, GETDATE());

SELECT @StartTime AS StartTime;

END;
GO
USE [SSIS]
GO

/****** Object: StoredProcedure [dbo].[SSISExecutionStopTime] Script Date: 20-03-2024

11:05:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSISExecutionStopTime]

(
@ENDTIME DATETIME OUTPUT
)
AS
BEGIN

--DECLARE @EndTime DATETIME;

SET @EndTime = CONVERT(DATETIME, GETDATE());

SELECT @EndTime AS EndTime;

END;
GO

19
SSIS Demo Project

The Detailed Explaination of SP

These stored procedures and the main procedure are designed to facilitate logging
SSIS (SQL Server Integration Services) package execution details into an
SSISExecutionLog table.

LogSSISExecution Procedure:
Purpose: This procedure is responsible for logging the execution details of an SSIS job
into the SSISExecutionLog table.

Parameters:
@NameOfTheJob: Represents the name of the SSIS job being executed.
Variables:
@StartTime: Stores the start time of the execution.
@EndTime: Stores the end time of the execution.
@DurationInSeconds: Stores the duration of the execution in seconds.
@Status: Indicates the status of the execution (1 for success, 0 for failure).
@Message: Stores a message indicating the status or any additional information.

Execution Flow:
Sets the @StartTime variable to the current date and time.
Calls two separate stored procedures, SSISExecutionStartTime and
SSISExecutionStopTime, to retrieve the start and end times of the execution,
respectively.
Calculates the duration of the execution in seconds.
Determines the status based on the start and end times.
Inserts the execution details along with the job name, start time, end time, duration,
status, and message into the SSISExecutionLog table.

SSISExecutionStartTime Procedure:
Purpose: This procedure retrieves the start time of the SSIS execution.
Parameters:
@StartTime: Output parameter to store the start time.
Execution Flow:
Sets the @StartTime parameter to the current date and time.
Returns the start time as the result.

20
SSIS Demo Project

SSISExecutionStopTime Procedure:
Purpose: This procedure retrieves the end time of the SSIS execution.
Parameters:
@EndTime: Output parameter to store the end time.
Execution Flow:
Sets the @EndTime parameter to the current date and time.
Returns the end time as the result.

Explanation:
The LogSSISExecution procedure manages the logging process by obtaining the start
and end times of the SSIS execution through separate stored procedures, calculating
the duration, determining the execution status, and finally inserting all relevant
details into the SSISExecutionLog table.

The SSISExecutionStartTime and SSISExecutionStopTime procedures acts as helper

procedures to provide the start and end times, respectively, for the main logging
procedure.

Together, these procedures create a logging mechanism for monitoring SSIS job
executions, capturing details such as timing, status, and any associated messages or
errors.

Other Projects

1. SQL Server Database backup

2. Data Insertion to SQL SERVER.
3. Null Insertion to SQL Server- Checking for anomalies when inserting null to SQL Server.
4. SQL Server to Width file Data migration.
5. Data Migration from CSV File.

21