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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics