February 13, 2014 at 12:19 am
Hi all,
I want to do error handling using Script task in contril flow task.
I have a table in my database to store information when any error occurs.
So, how can I insert error message from SSIS into my database table?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 13, 2014 at 1:36 am
I tried in this manner but its not working..............
Public Sub Main()
Dim iErrorCode As Integer = _
Convert.ToInt32(Dts.Variables("ErrorCode").Value)
Dim sErrorDescription As String = _
Dts.Variables("ErrorDescription").Value.ToString
Dim sSourceName As String = _
Dts.Variables("SourceName").Value.ToString
Dim sSubComponent As String = _
"OnError Event Handler"
'Dim sMsg As String = "Source: " & sSourceName & vbCrLf & _
' "Error Code: " & iErrorCode.ToString & _
' vbCrLf & _
' "Error Description: " & _
' sErrorDescription
'MsgBox(sMsg, , sSubComponent)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
myConnection = New SqlConnection("server = localhost ;uid = sa; pwd = 123; database = eCareProDB")
myConnection.Open()
myCommand = New SqlCommand("INSERT INTO DATALOAD_ERROR_LOG(EL_ErrorCode,EL_ErrorDescription) VALUES (iErrorCode, sErrorDescription)")
myCommand.ExecuteNonQuery()
myConnection.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
Plese suggeset
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 13, 2014 at 2:03 am
Did you try using SSIS log provider for SQL server ?
February 13, 2014 at 2:07 am
Please take a look at
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/95353/
February 13, 2014 at 2:18 am
But I have to insert in sql table only...
I am getting error in my code below:
Public Sub Main()
Dim iErrorCode As Integer = _
Convert.ToInt32(Dts.Variables("ErrorCode").Value)
Dim sErrorDescription As String = _
Dts.Variables("ErrorDescription").Value.ToString
Dim sSourceName As String = _
Dts.Variables("SourceName").Value.ToString
Dim sSubComponent As String = _
"OnError Event Handler"
'Dim sMsg As String = "Source: " & sSourceName & vbCrLf & _
' "Error Code: " & iErrorCode.ToString & _
' vbCrLf & _
' "Error Description: " & _
' sErrorDescription
'MsgBox(sMsg, , sSubComponent)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
myConnection = New SqlConnection("server = localhost ;uid = sa; pwd = 123; database = eCareProDB")
myConnection.Open()
myCommand = New SqlCommand("INSERT INTO DATALOAD_ERROR_LOG(EL_ErrorCode,EL_ErrorDescription) VALUES (" + Dts.Variables(iErrorCode).Value + "," + Dts.Variables(sErrorDescription).Value + ")", myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
'MsgBox(myConnection, MsgBoxStyle.Exclamation)
Dts.TaskResult = ScriptResults.Success
End Sub
Error:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 13, 2014 at 3:08 am
can someone tell me why I am getting this error:
Conversion from string "INSERT INTO DATALOAD_ERROR_LOG(E" to type 'Double' is not valid.
Both columns EL_ErrorCode,EL_ErrorDescription are of datatype nvarchar(1000)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply