How to use SQLConnection in Script Task ?

Dear all,

I hava a Script Task to log an error if it occurs. I want to log the error into a table. I used sqlconnection object and sqlcommand object. This is my script inside the task :

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub

I thought my script was correct, but when I run the package there's an error, saying like this :
'object reference not set to an instance of an object'.
Where's the problem anyway ?
Thanks in advance,

Best regards,

Hery

Hi, though the code seems free of errors other than - varConnStr not declared

Please check the following things

1) Try Individually Running the Script Task, if it runs successfully, Package might have faied due to other reasons.

2) Else There might be some problem establishing connection in the Script, please add a messagebox just below conn.open

3) You might also add one more messagebox just below cmd.ExecuteNonQuery() to check or see the table if the new row has been inserted.

Many Thanks

Subhash Subramanyam

|||Hi Subhash,
thanks for the reply...
I've just found out the error, there's a silly mistake that I made by myself, in previous script I didn't put the Dts.ExecutionValue.ToString(), actually I did :-(
When I remove that line, the script worked successfully...sorry for this silly question..

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ";" + Dts.ExecutionValue.ToString() + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub

Best regards,

Hery
|||

Hi,

Please mark your thread as answered.

Thanks

Subhash Subramanyam