Convert the VB script to VB .net

[ParthaSarathi Prusty]

Hi,

Somebody please help me to convert the Vb script to vb .net. Most of the code has been already converted. Please help me to convert the rest of the code. Also, i am getting some error. Please help me to resolve it.

Public Sub Main()

Dim PTSFeedQueryRecordset As New ADODB.Recordset()
Dim PTSFeedQueryRecordsetMarshalls As ADODB.Recordset()
Dim dr As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
Dim dt1 As New DataTable
Dim intRunDuration As Integer
Dim intRunCount As Integer
Dim intStepId As Integer
Dim i As Integer
Dim arrPTSFeedQueryRecordset As VariantType
Dim prevStepName, prevStepId, strStepName As VariantType
Dim bReduceCount As Boolean
Dim strJobStatus As Boolean

dr.Fill(ds, Dts.Variables("User:TSFeedQueryRecordsetMarshalls").Value)

If Not (ds Is Nothing) Then

intRunDuration = 0
bReduceCount = False
dt = ds.Tables.Add
dt.Select("step_id=99")
intRunCount = dt.Rows.Count

If IsArray(Dts.Variables("arrPTSFeedQueryRecordsetMarshalls")) Then
For i = LBound(Dts.Variables("User::arrPTSFeedQueryRecordsetMarshalls").Value, 2) To UBound(Dts.Variables("User::arrPTSFeedQueryRecordsetMarshalls").Value, 2)
intStepId = Dts.Variables("User::arrPTSFeedQueryRecordsetMarshalls").Value(0, i)
If intRunCount > 1 Then
If intStepId = 99 Then
bReduceCount = True
End If
ElseIf intRunCount = 1 Then Get the values only when the RunCount is 1
intRunDuration = intRunDuration + Dts.Variables("arrPTSFeedQueryRecordsetMarshalls").Value(2, i)
If intStepId = 5 Then
strJobStatus = True
End If
If intStepId = 6 Then
strJobStatus = False
strStepName = Dts.Variables("arrPTSFeedQueryRecordsetMarshalls").Value(1, i - 1)
prevStepId = Dts.Variables("arrPTSFeedQueryRecordsetMarshalls").Value(0, i - 1)
End If
If intStepId = 99 Then
Exit For
End If
If bReduceCount = True Then
intRunCount = intRunCount - 1
bReduceCount = False
End If
End If
Next i
Erase Dts.Variables("arrPTSFeedQueryRecordsetMarshalls").Value
End If
End If
intRunDuration = Int((intRunDuration / 10000)) & " Hr " & Int(intRunDuration / 100 Mod 100) & " Min " & Int(intRunDuration Mod 100) & " Sec "
SendMail(strJobStatus, intRunDuration, prevStepId, strStepName)
End Sub


Public Sub SendMail(ByVal JobStatus As Boolean, ByVal RunDuration As Integer, ByVal StepId As Integer, ByVal StepName As VariantType)

Dim iMsg As String
Dim iConf As String
Dim mailBody As String
Dim strHTML As String
Dim strSmartHost, strFromMars, strSubjectMarshallsSuccess, strSubjectMarshallsFail As String
Dim strTo, strFrom, strSubject, strBody, strMarsLogdir As String
Dim dr As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable

Const cdoSendUsingPort As Integer = 2

strMarsLogdir = Dts.Variables("User::strMarsLogdir").Value

Dim strBodySuccess As VariantType = " </br> PTS_ImportPOCCFromMainframeMars job for Marshalls got executed successfully at " & DateTime.Now & ". </br>The total run duration for the job is: " & RunDuration & ".</br></br></br>PTS_ImportPOCCFromMainframeMars"
Dim strBodyFail As VariantType = "</br> PTS_ImportPOCCFromMainframeMars job for Marshalls failed at Step No. " & StepId & " - " & StepName & " on " & Now & ".</br> The log file can be retrieved here:</br> " & strMarsLogdir & "</br>PTS_ImportPOCCFromMainframeMars"

strSmartHost = Dts.Variables("User::strSmartHost").Value
strTo = Dts.Variables("User::strTo").Value
strFromMars = Dts.Variables("User::strFromMars").Value
strSubjectMarshallsSuccess = Dts.Variables("User::strSubjectMarshallsSuccess").Value & " " & DateTime.Now
strSubjectMarshallsFail = Dts.Variables("strSubjectMarshallsFail").Value & " " & DateTime.Now

iMsg = CreateObject("CDO.Message")
iConf = CreateObject("CDO.Configuration")
Flds = iConf.Fields
dr.Fill(ds, Dts.Variables("iConf").Value)

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmartHost
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update()
End With

apply settings to the message
With iMsg
.Configuration = iConf
.To = strTo
.From = strFromMars


If JobStatus Then
.Subject = strSubjectMarshallsSuccess
.HTMLBody = strBodySuccess
.Fields.Update()
.Send Commented so that success mail does not go #IM2246946
Else
.Subject = strSubjectMarshallsFail
.HTMLBody = strBodyFail
.Fields.Update()
.Send()
End If
End With


cleanup of variables
iMsg = Nothing
iConf = Nothing
Flds = Nothing


Dts.TaskResult = ScriptResults.Success
End Sub

Continue reading...