Problem
:
We extracted the data from Excel and load to Oracle database , sometimes the
flow failed due to data issue , like length of field in Excel exceed the
database length of datatype. we have to track those failed records and see what
is the error message , why they are failed. this is row level error message
tracking
Solution :
1 create a pl/sql procedure to extract the error message based on the error
code
CREATE OR REPLACE PROCEDURE BIDW.GET_ERROR_DESCRIPTION (
p_Error_Code IN NUMBER,
x_Error_Description OUT VARCHAR2,
x_return_code OUT NUMBER,
x_return_msg OUT VARCHAR2
)
IS
BEGIN
x_Error_Description := SQLERRM(p_Error_Code*-1);
x_return_code := 0;
EXCEPTION
WHEN OTHERS
THEN
x_return_code := -1;
x_return_msg := SQLERRM;
--DBMS_OUTPUT.put_line
('Others :' || SQLERRM);
END GET_ERROR_DESCRIPTION;
2 direct the destination error point to a
script component and change the error option to Redirect row
3 edit the script property
A select input column, only select ErrorCode , this was
automatically generated
B manually add one output
C add code
In this case , we use a parameter as connection string
Public Overrides Sub Input0_ProcessInputRow(ByVal
Row As Input0Buffer)
'
' Add your code here
'
Dim cn As OleDbConnection = New
OleDbConnection()
Dim cmd As OleDbCommand = New
OleDbCommand()
'cn.ConnectionString =
Connections.Connection.ConnectionString.ToString
cn.ConnectionString = Variables.ConnectionStringOraOLEDBBI.ToString
cn.Open()
cmd.Connection = cn
cmd.CommandText = "BIDW.GET_ERROR_DESCRIPTION"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("P_ERROR_CODE",
OleDbType.VarNumeric).Value = Row.ErrorCode
cmd.Parameters.Add("X_ERROR_DESCRIPTION",
OleDbType.VarChar, 500).Direction = ParameterDirection.Output
cmd.Parameters.Add("X_RETURN_CODE",
OleDbType.VarNumeric).Direction = ParameterDirection.Output
cmd.Parameters.Add("X_RETURN_MSG",
OleDbType.VarChar, 500).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
If (cmd.Parameters("X_RETURN_CODE").Value
= 0) And (InStr(cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString, "non-ORACLE exception", CompareMethod.Text) = 0) And
(InStr(cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString,
"User-Defined Exception", CompareMethod.Text) = 0) Then
Row.ErrorDescription = cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString
Else
Row.ErrorDescription =
ComponentMetaData.GetErrorDescription(Row.ErrorCode).ToString
End If
cn.Close()
End Sub
End Class
4 load the records to flat file, which caused the error
Three new columns added
ErrorCode and ErrorColumn are generated by red Error Output
, the ErrorDescription is generated by script , which called a procedure
5 sent the error log file to someone , by using .net code
read only variable: User::varErrorFile,$Project::varMailFrom,$Project::varMailSuccSub,
$Project::varJobEnvironment,$Project::varMailTo_Success,$Project::varMailSMTPServer
Public Sub Main(
Dim strCommandLineArgs As
String = String.Empty
Dim strSub As String = Convert.ToString(Dts.Variables("$Project::varMailSuccSub").Value)
Dim strEmailTo As String = Convert.ToString(Dts.Variables("$Project::varMailTo_Success").Value)
Dim strEmailFrom As String = Convert.ToString(Dts.Variables("$Project::varMailFrom").Value)
Dim strEnv As String = Convert.ToString(Dts.Variables("$Project::varJobEnvironment").Value)
Dim strSMTPServer As String = Convert.ToString(Dts.Variables("$Project::varMailSMTPServer").Value)
Dim filePath As String = Convert.ToString(Dts.Variables("varErrorFile").Value)
Dim lineCount As Integer = 0
Try
If (File.Exists(filePath))
Then
Dim the_stream As
StreamReader = New
StreamReader(filePath)
While (Not
the_stream.EndOfStream)
the_stream.ReadLine()
lineCount = lineCount + 1
End While
the_stream.Close()
End If
Catch ex As Exception
End Try 'check the file is existing or not
If (lineCount > 1) Then ' check whether there are data in the file
Try
Dim smtp As
New SmtpClient(strSMTPServer)
With smtp
End With
Dim message As
New MailMessage
Dim strFile As
Attachment
strFile = New Attachment(Convert.ToString(Dts.Variables("varErrorFile").Value))
Dim strFilePath As
String = Convert.ToString(Dts.Variables("varErrorFile").Value)
strCommandLineArgs = "List of the
records in error during processing of data from enrichment file –("
+ strFilePath.Substring(strFilePath.LastIndexOf("\")
+ 1, (strFilePath.Length - 4) - strFilePath.LastIndexOf("\")) + ")"
+ " at " + Date.Now.ToString() + " on "
+ strEnv + " Environment. "
With message
.IsBodyHtml = True
.Subject = "PFA " +
strCommandLineArgs
.To.Add(strEmailTo)
.From = New MailAddress(strEmailFrom)
.Body = strCommandLineArgs
.Attachments.Add(strFile)
End With
smtp.Send(message)
Catch ex
As Exception
' MsgBox(ex.Message)
End Try
End If
Dts.TaskResult = ScriptResults.Success
End Sub
