SSIS provides inbuilt logging option to trace the audit information in text file or SQL table while executing the SSIS package. But if we need to retrieve all the information from single file or table it is not easy task when we use inbuilt option in SSIS package.
For instance, we are going to load the data from flat file to SQL table. We need to trace all the information to single table like when the package started, finished and how many rows are inserted, rejected during the execution. We can go for customized logs in SSIS to perform this scenario.
For instance, we are going to load the data from flat file to SQL table. We need to trace all the information to single table like when the package started, finished and how many rows are inserted, rejected during the execution. We can go for customized logs in SSIS to perform this scenario.
Table Structure for Customized Logs in SSIS
We need to create two different tables in destination SQL database which listed as below.
CREATE TABLE [dbo].[PackageLog]
(
[Execution_ID] UNIQUEIDENTIFIER NOT NULL,
[Package_ID] UNIQUEIDENTIFIER NOT NULL,
[Package_Description] VARCHAR(50) NULL,
[Source_Name] VARCHAR(50) NULL,
[File_Name] VARCHAR(50) NULL,
[Records_Inserted] INT NOT NULL DEFAULT (0),
[Records_Rejected] INT NOT NULL DEFAULT (0),
[Package_Status] BIT NOT NULL DEFAULT (0),
[User_Name] VARCHAR(50) NULL,
[Machine_Name] VARCHAR(50) NULL,
[Start_DateTime] DATETIME NOT NULL DEFAULT GETDATE(),
[End_DateTime] DATETIME NULL
)
The Package Log table holds the information about when the package has started, completed and how many rows are affected during the process.
CREATE TABLE [dbo].[ErrorLog]
(
[Execution_ID] UNIQUEIDENTIFIER NOT NULL,
[Source_Name] VARCHAR(250) NULL,
[File_Name] VARCHAR(50) NULL,
[Error_Message] VARCHAR(8000) NULL,
[Error_Code] VARCHAR(25) NULL,
[Log_DateTime] DATETIME NULL DEFAULT GETDATE(),
[Error_Type] VARCHAR(20) NULL
)
The Error Log table holds Error code, Error type and Error message information if the package or task failed during the process.
Apply Event Handlers to Customized Log tables
We need to create variables such as File name, SMTP Port, Records inserted and Records Rejected in variable window.
Package Log Table
Step 1: Go to Event Handlers of first task which we created in control flow.
Step 2: Choose OnPreExecute from the drop down under the Event handler and ensure the executable drop down selected our first task name.
Step 3: Drop and drag the Execute SQL Task from SSIS Toolbox and define the connection and paste below mentioned query on SQL Statement text box in task editor.
INSERT INTO [dbo].[PackageLog]
(
[Execution_ID], [Package_ID], [Package_Description], [Source_Name], [File_Name], [User_Name], [Machine_Name]
)
VALUES
(
@ExecutionID, @PackageID, @PackageName, @SourceName, @FileName, @UserName, @MachineName
)
Step 4: Map the parameters using Parameter Mapping such as System::ExecutionInstanceGUID, System::PackageID, System::PackageName, System::SourceName, File Name (User defined Variable), System::UserName and System::MachineName variables.
Step 5: Go to Event Handlers of last task which we created in control flow if the first and last task are different in control flow.
Step 6: Choose OnPostExecute from the drop down under the Event handler and ensure the executable drop down selected our last task name.
Step 7: Drop and drag the Execute SQL Task from SSIS Toolbox and define the connection and paste below mentioned query on SQL Statement text box in task editor.
DECLARE @ExecuctionID UNIQUEIDENTIFIER
DECLARE @FileName VARCHAR(50)
DECLARE @RecordsInserted INT
DECLARE @RecordsRejected INT
SET @ExecuctionID = ?
SET @FileName = ?
SET @RecordsInserted = ?
SET @RecordsRejected = ?
UPDATE [PackageLog] SET
[Records_Inserted] = @RecordsInserted,
[Records_Rejected] = @RecordsRejected,
[Package_Status] = (SELECT (CASE WHEN COUNT(1) > 0 THEN 0 ELSE 1 END) FROM [dbo].[ErrorLog] WHERE [Execution_ID] = @ExecuctionID),
[End_DateTime] = GETDATE()
WHERE [Execution_ID] = @ExecuctionID
AND [File_Name] = @FileName
Step 8: Map the parameters using Parameter Mapping such as System::ExecutionInstanceGUID, File Name (User defined variable), Records Inserted (User defined variable), Records Rejected (User defined variable).
Error Log Table
Step 9: Go to Event Handlers of control flow, choose OnError from the drop down under the Event handler and ensure the executable drop down selected our package name.
Step 10: Drop and drag the Execute SQL Task from SSIS Toolbox and define the connection and paste below mentioned query on SQL Statement text box in task editor.
DECLARE @Execution_ID UNIQUEIDENTIFIER
DECLARE @Source_Name VARCHAR(50)
DECLARE @File_Name VARCHAR(50)
DECLARE @ErrorDescription VARCHAR(8000)
DECLARE @ErrorCode VARCHAR(25)
SET @Execution_ID = ?
SET @Source_Name = ?
SET @File_Name = ?
SET @ErrorDescription = ?
SET @ErrorCode = ?
INSERT INTO ErrorLog
(
[Execution_ID], [Source_Name], [File_Name], [Error_Message], [Error_Code]
)
VALUES
(
@Execution_ID, @Source_Name, @File_Name, @ErrorDescription, @ErrorCode
)
Stored Procedure to retrieve the data from Log Tables
CREATE PROCEDURE [dbo].[USP_EventMail_Notification]
(
@EXECUTION_ID VARCHAR(40)
)
AS
BEGIN
BEGIN TRY
SELECT PL.[Package_Description] AS PACKAGE_NAME,
CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, START_DATETIME, END_DATETIME), 0), 114) AS TIME_ELAPSED
, START_DATETIME, END_DATETIME, PL.SOURCE_NAME, 'File Name : ' + EL.SOURCE_NAME + ' - ' + EL.ERROR_MESSAGE AS ERROR_MSG, ERROR_TYPE,
RECORDS_INSERTED, RECORDS_REJECTED
FROM [PackageLog] PL
LEFT JOIN [ErrorLog] EL ON (EL.EXECUTION_ID = PL.EXECUTION_ID)
WHERE PL.EXECUTION_ID = @EXECUTION_ID
SELECT COUNT(1) AS ERROR_COUNT FROM ErrorLog WHERE EXECUTION_ID = @EXECUTION_ID
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
Create a string variable in Variables window named EmailHeader and paste below HTML code. We can customize this code to change different themes in email notification.
<html>
<head>
</head>
<style type='text/css'>
body {
color: #666;
font: 12px/24px "Open Sans", "HelveticaNeue-Light", "Helvetica Neue Light", "Helvetica Neue", Helvetica, Arial, "Lucida Grande", Sans-Serif;
}
table {
border-collapse: separate;
border-spacing: 0;
width: 100%;
}
th, td {
padding: 6px 15px;
}
th {
background: #42444e;
color: #fff;
text-align: center;
}
tr:first-child th:first-child {
border-top-left-radius: 6px;
}
tr:first-child th:last-child {
border-top-right-radius: 6px;
}
td {
border-right: 1px solid #c6c9cc;
border-bottom: 1px solid #c6c9cc;
}
td:first-child {
border-left: 1px solid #c6c9cc;
}
tr:nth-child(even) td {
background: #eaeaed;
}
tr:last-child td:first-child {
border-bottom-left-radius: 6px;
}
tr:last-child td:last-child {
border-bottom-right-radius: 6px;
}
</style>
<body>
<table width="100%" align='center' border='0' cellspacing='0' cellpadding='0' style='font-size: 13pt'>
<thead>
<tr>
<th align='center'>Notifications</th>
</tr>
</thead>
</table>
<br />
<table border='0' width='100%' align='center' cellspacing='0' cellpadding='0'>
<thead>
<tr>
<th scope="col">Date</th>
<th scope="col">Package Name</th>
<th scope="col">File Name</th>
<th scope="col">Type</th>
<th scope="col">Information</th>
</tr>
</thead>
We have to define the HTML email notification using script task based on the Package and Error tables information.
Drag and drop the script task and navigate precedence constraint from last task which we created in control flow.
Note:
We need to add the reference Microsoft.SQLServer.DTSRunTimeWrap in script task.
The code reads OLEDB connection string (Destination) from connection manager.
The code reads SMTP connection properties (SMTP Connection Manager) from connection manager.
The code reads OLEDB connection string (Destination) from connection manager.
The code reads SMTP connection properties (SMTP Connection Manager) from connection manager.
ScriptMain.cs
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text;
using System.Data.OleDb;
using System.Net.Mail;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
//Paste below code after public void Main()
try
{
//Declaring the Variables
int totalRejected = 0;
int totalInserted = 0;
string packageName = string.Empty;
string fileName = string.Empty;
int smtpPort = int.Parse(Dts.Variables["$Project::SMTPPort"].Value.ToString());
System.Text.StringBuilder emailBody = new System.Text.StringBuilder();
StringBuilder errorMsg = new StringBuilder();
string InfoMsg = string.Empty;
string emailFrom = Dts.Variables["$Project::EmailFrom"].Value.ToString();
string emailTo = Dts.Variables["$Project::EmailTo"].Value.ToString();
string emailheader = Dts.Variables["EmailHeader"].Value.ToString();
string emailSubject = string.Empty;
string timeElapsed = string.Empty;
string packageStart = string.Empty;
string packageEnd = string.Empty;
int errorsCount = 0;
string ExecutionID = Dts.Variables["ExecutionInstanceGUID"].Value.ToString();
IDTSConnectionManagerDatabaseParameters100 connMrgParameters = default(IDTSConnectionManagerDatabaseParameters100);
System.Data.OleDb.OleDbConnection oledbConn = null;
ConnectionManager connMgr = Dts.Connections["Destination"];
DataTable dtPackageLog = new DataTable();
DataTable dtErrorLog = new DataTable();
//Reading the Connection manager Properties and assigning to variables
connMrgParameters = (IDTSConnectionManagerDatabaseParameters100)connMgr.InnerObject;
oledbConn = connMrgParameters.GetConnectionForSchema() as OleDbConnection;
DataSet dsPackageDetail = new DataSet();
//Establishing the Oledb connection and reading the logs tables using stored procedure
using (System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(oledbConn.ConnectionString))
{
dbConnection.Open();
OleDbCommand oledbcmd = new OleDbCommand();
oledbcmd.Connection = dbConnection;
oledbcmd.CommandText = "USP_EventMail_Notification";
oledbcmd.CommandType = CommandType.StoredProcedure;
//oledbcmd.Parameters.Add("DATASTREAM_ID", OleDbType.Integer).Value = streamID;
oledbcmd.Parameters.Add("EXECUTION_ID", OleDbType.VarChar).Value = ExecutionID;
OleDbDataAdapter adap = new OleDbDataAdapter(oledbcmd);
adap.Fill(dsPackageDetail);
}
dtPackageLog = dsPackageDetail.Tables[0];
dtErrorLog = dsPackageDetail.Tables[1];
errorsCount = Convert.ToInt32(dsPackageDetail.Tables[2].Rows[0]["ERROR_COUNT"]);
// Assigning Email contents
//packageName = dtPackageLog.Rows[0]["PACKAGE_NAME"].ToString();
packageName = "ADP Staging to MDS Load";
//fileName = dtPackageLog.Rows[0]["FILE_SOURCE"].ToString();
emailSubject = Dts.Variables["$Project::EmailSubject_MDSLoad"].Value.ToString(); //Email Notifiation from MDS Load SSIS package
if (errorsCount > 0)
{
emailSubject = Dts.Variables["$Project::EmailSubject_MDSLoad"].Value.ToString() + " with " + errorsCount + " Error(s)";
}
emailBody.Append(emailheader);
if (dtPackageLog.Rows.Count > 0)
{
//Adding Email row based on the Package and Error log table
for (int i = 0; i < dtPackageLog.Rows.Count; i++)
{
totalInserted = Convert.ToInt32(dtPackageLog.Rows[i]["RECS_INSERTED"]);
totalRejected = Convert.ToInt32(dtPackageLog.Rows[i]["RECS_REJECTED"]);
timeElapsed = dtPackageLog.Rows[i]["TIME_ELAPSED"].ToString();
packageStart = dtPackageLog.Rows[i]["START_DATETIME"].ToString();
packageEnd = dtPackageLog.Rows[i]["END_DATETIME"].ToString();
errorMsg.Length = 0;
if (totalInserted > 0)
{
emailBody.Append("<tr><td>" + System.DateTime.Now.ToString() + "</td>");
emailBody.Append("<td>" + packageName + "</td>");
emailBody.Append("<td>" + dtPackageLog.Rows[i]["SOURCE_NAME"] + "</td>");
emailBody.Append("<td> Info </td><td>");
emailBody.Append(errorsCount > 0 ? "No of Errors: " + totalRejected : "No Errors ");
emailBody.Append(" (Total Time:" + timeElapsed + ")");
emailBody.Append("<br>No. Of Records Affected : " + totalInserted);
emailBody.Append("<br>SSIS Processing Start Time : " + packageStart);
emailBody.Append("<br>SSIS Processing End Time : " + packageEnd);
emailBody.Append("</td></tr>");
}
if (totalRejected > 0)
{
string expression = "SOURCE_NAME = '" + dtPackageLog.Rows[i]["SOURCE_NAME"].ToString() + "'";
//DataTable tmpErrorLog = new DataTable();
DataRow[] foundRows = dtErrorLog.Select(expression);
errorMsg.Append("Errors: " + totalRejected + " ");
errorMsg.Append("(Total Time:" + timeElapsed + ")");
errorMsg.Append("<ol>");
for (int j = 0; j < foundRows.Length; j++)
{
errorMsg.Append("<li>" + foundRows[j]["ERROR_MSG"].ToString() + "</li>");
}
errorMsg.Append("</ol>");
emailBody.Append("<tr><td>" + System.DateTime.Now.ToString() + "</td>");
emailBody.Append("<td>" + packageName + "</td>");
emailBody.Append("<td>" + dtPackageLog.Rows[i]["SOURCE_NAME"] + "</td>");
emailBody.Append("<td> Error </td>");
emailBody.Append("<td>" + errorMsg + "</td></tr>");
}
}
}
else
{
emailBody.Append("<tr><td class='borderleft' colspan ='5' align='center'>Records not available in Staging Database.</td></tr>");
}
emailBody.Append("</table></body></html>");
// Establishing SMTP connections from connection manager
MailMessage msg = new MailMessage(emailFrom, emailTo, emailSubject, emailBody.ToString());
msg.IsBodyHtml = true;
String SmtpServer = Dts.Connections["SMTP Connection Manager"].Properties["SmtpServer"].GetValue(Dts.Connections["SMTP Connection Manager"]).ToString();
SmtpClient smtp = new SmtpClient(SmtpServer);
//smtp.Credentials = CredentialCache.DefaultNetworkCredentials;
smtp.UseDefaultCredentials = false;
smtp.Port = smtpPort;
smtp.Send(msg);
}
catch (Exception ex)
{
Dts.Events.FireError(-1, "Sending Email from Script Task", ex.Message, String.Empty, 0);
}HTML Email Output
Also see below articles
Top 20 SSIS Interview Questions with detailed answers
How to apply dynamic SQL Query on OLEDB Command in SSIS Package
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
Export ISPAC file and Extract SSIS packages from Integration Services Catalogs in deployed folder
Top 20 SSIS Interview Questions with detailed answers
How to apply dynamic SQL Query on OLEDB Command in SSIS Package
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
Export ISPAC file and Extract SSIS packages from Integration Services Catalogs in deployed folder
No comments:
Post a Comment