Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error handling Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 12:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1541040
Posted Thursday, February 13, 2014 1:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1541059
Posted Thursday, February 13, 2014 2:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:39 AM
Points: 470, Visits: 483
Did you try using SSIS log provider for SQL server ?
Post #1541065
Posted Thursday, February 13, 2014 2:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:39 AM
Points: 470, Visits: 483
Please take a look at
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/95353/
Post #1541067
Posted Thursday, February 13, 2014 2:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1541070
Posted Thursday, February 13, 2014 3:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1541083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse