getting error in SSIS: Creating Error Log File
Hello Everyone,
i hav designed Error Log File using this example but i m getting error i m pasting my error at last.
When we desing the package it is required to create an error log file for reference if any error occured during the execution of package. Here the error log file is unique for every execution. OnError event will catch the error details if it ocure on package
further by using Script task we can write these details on text/log file. to do this task we need to follow these steps:
Create variables to provide he physical location of log file Write a script to log the error description on text file
Step1: Creating Variable
> ErrorLogPath (Type: String): That would be configured by configuration file externaly for example C:\App\Log\
> ErrorLogFile (Type: String): It is expression based. To set the expression select the variable -> Press F4 to see the property Set IsExpression=True and write the following code for expression:
@[User::ErrorLogPath]+"PackageErrorLog"+"_"+(DT_WSTR, 4) DATEPART( "yyyy", @[System::StartTime]) +(DT_WSTR, 2) DATEPART( "mm", @[System::StartTime]) +(DT_WSTR, 2) DATEPART( "dd", @[System::StartTime]) +"_"+(DT_WSTR, 2) DATEPART( "hh",@[System::StartTime])
+(DT_WSTR, 2) DATEPART( "minute",@[System::StartTime]) +(DT_WSTR, 2) DATEPART( "ss", @[System::StartTime]) +".txt"
basically here I have concatenate the ErrorLogPath with ErrorlLogfile name that is combination of datetime and the file type is ".txt", So when ever the package is executed a unique file name is generated.
Example: where " C:\App\Log\" is ErrorLogPath
C:\App\Log\PackageErrorLog_2009618_11432.txt
Step 2: Writing error Log
Select OnError Event of Package -> Drag and drop an Script Task -> Right click on script taks and click on Edit. the Script Task Editor will be appear -> Type the "ErrorLogFile" variable name in read only variables section -> at bottom click
on Script to write the script. the VSA editor will be appear where I hae to write the code. Inside the class i have written the following code:
Public Sub Main()
Dim errNumber As Integer
Dim errDescription As String
Dim errSource As String
Dim log As New System.IO.StreamWriter(Dts.Variables("ErrorLogFile").Value.ToString(), True)
Try
GetErrorValues(errNumber, errDescription, errSource)
log.WriteLine("ErrorDateTime: " + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString())
log.WriteLine("ErrorCode: " & errNumber.ToString())
log.WriteLine("ErrorSource: " & errSource)
log.WriteLine("ErrorDescription: " & errDescription)
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.ToString())
Finally
log.Close()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub GetErrorValues(ByRef errNumber As Integer, _
ByRef errDescription As String, ByRef errSource As String)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead("ErrorCode")
Dts.VariableDispenser.LockForRead("ErrorDescription")
Dts.VariableDispenser.LockForRead("SourceName")
Dts.VariableDispenser.GetVariables(vars)
Try
errNumber = CType(vars("ErrorCode").Value, Integer)
errDescription = vars("ErrorDescription").Value.ToString()
errSource = vars("SourceName").Value.ToString()
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As SystemException
Throw ex
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Save and exit from VSA editor -> close the Script Editor -> Provide some space for error and execute the package to get the error log.
I m getting error after runnin the script.
DTX Script task has encountered an exception in user code
Project name:Scriptask_de9f2cdc
The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
at Microsoft.SqlServer at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_de9fdc4182df444887755419a79dcd87.ScriptMain.Main()Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_de9fdc4182df444887755419a79dcd87.ScriptMain.Main()
I m not getting wht to do with this error code can anyone provide me some help, or any link which will be helpful for me .
June 18th, 2011 1:11am
The issue seems to be in the scope of the OnError event handler. Make sure you defined it at the package level.
Otherwise, and I have an impression, the Script Task runs regardless and since there was no error, of course the error variables array is empty and thus the error.
It is also a good practice in coding to add a check to make sure the variable is initialized before it is being consumed.
And just in case you are in doubt follow this post for the exact steps on how to implement the On Error logging to file:
http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2007/04/08/ssis-design-pattern-custom-error-handling.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 19th, 2011 1:14am
Thanks a lot ur link helped me lot
June 20th, 2011 1:29am