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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy