' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables,
events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to
indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'

Dim pTableName As String
Dim pSequence As Int32

pTableName = CStr(Dts.Variables("TableName").Value)
pSequence = CInt(Dts.Variables("Sequence").Value)

Dim lMsgTime As DateTime
Dim lJobID As Int16
Dim lLoadType As Int16
Dim lDescription As String
Dim lSource As String
Dim lEventName As String
Dim lMsgType As String
Dim lNumRecords As Integer
Dim lMsgStatus As String

If pTableName = "Patients" Then
If pSequence = 1 Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
Else
Dts.TaskResult = Dts.Results.Failure
End If

If Dts.TaskResult = Dts.Results.Success Then
lMsgTime = Now
lJobID = CShort(pSequence)
lLoadType = 100
lDescription = "Job ran successfully"
lSource = "YKCLNSD"
lEventName = "Dts.Results.Success"
lMsgType = pTableName	'"Success message"
lNumRecords = 1000
lMsgStatus = "Sent"
ElseIf Dts.TaskResult = Dts.Results.Failure Then
lMsgTime = Now
lJobID = CShort(pSequence)
lLoadType = 100
lDescription = "Job failed"
lSource = "YKCLNSD"
lEventName = "Dts.Results.Failure"
lMsgType = pTableName	'"Failure message"
lNumRecords = 0
lMsgStatus = "Sent"
End If

'Dts.Variables("MsgTime").Value = lMsgTime
'Dts.Variables("JobID").Value = lJobID
'Dts.Variables("LoadType").Value = lLoadType
'Dts.Variables("Description").Value = lDescription
'Dts.Variables("Source").Value = lSource
'Dts.Variables("EventName").Value = lEventName
'Dts.Variables("MsgType").Value = lMsgType
'Dts.Variables("NumRecords").Value = lNumRecords
'Dts.Variables("MsgStatus").Value = lMsgStatus

Dim xWriter As New
XmlTextWriter("\\YKCLNSD\ETLEngineTest\StatusMessageCopy.xml",
System.Text.Encoding.UTF8)

xWriter.Formatting = Formatting.Indented
xWriter.Indentation = 2

xWriter.WriteStartDocument(True)
xWriter.WriteStartElement("ns0:Status")
xWriter.WriteAttributeString("xmlns", "ns0", Nothing,
"http://ETLOrchestration.StatusMessage")

xWriter.WriteStartElement("MessageDateTime")
xWriter.WriteValue(lMsgTime)
xWriter.WriteEndElement()

xWriter.WriteStartElement("JobID")
xWriter.WriteValue(lJobID)
xWriter.WriteEndElement()

xWriter.WriteStartElement("LoadType")
xWriter.WriteValue(lLoadType)
xWriter.WriteEndElement()

xWriter.WriteStartElement("MessageDescription")
xWriter.WriteValue(lDescription)
xWriter.WriteEndElement()

xWriter.WriteStartElement("MessageSource")
xWriter.WriteValue(lSource)
xWriter.WriteEndElement()

xWriter.WriteStartElement("MessageEventName")
xWriter.WriteValue(lEventName)
xWriter.WriteEndElement()

xWriter.WriteStartElement("MessageType")
xWriter.WriteValue(lMsgType)
xWriter.WriteEndElement()

xWriter.WriteStartElement("MessageStatus")
xWriter.WriteValue(lMsgStatus)
xWriter.WriteEndElement()

xWriter.WriteStartElement("NumberOfRecordsAffected")
xWriter.WriteValue(lNumRecords)
xWriter.WriteEndElement()

xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Flush()
xWriter.Close()

End Sub

End Class