derek:b3studios

oRemote.sqlServer(False) = GetServer()
oRemote.useProxy("ProxyName") = True
oRemote.username = "Username"
oRemote.password = "Password"
oRemote.dtsCommand = DtsCommand
If oRemote.CreateJob() Then
oRemote.RunJob()
End If

Imports System.Data
Imports System.Data.Sql
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Common

Public Class RemoteSSIS

#Region "Variables"
'entry variables
Private strSqlServer As String = Nothing
Private strDtsCommand As String = Nothing
Private strProxyName As String = Nothing
Private boolUseProxy As Boolean = False
Private boolSecureConnection As Boolean = False
Private strUsername As String = Nothing
Private strPassword As String = Nothing
Private jobCreated As Boolean = False
Private jobRan As Boolean = True
Private jobCleared As Boolean = True
Private strjobStatus As String = Nothing
Private boolisfinished As Boolean = False

'server objects
Private serverConn As ServerConnection = New ServerConnection
Private svr As Server
Private js As JobServer
Private jb As Job
#End Region

#Region "Properties"

Public Property sqlServer(ByVal secureConnection As Boolean) As String
Get
Return strSqlServer
End Get
Set(ByVal value As String)
strSqlServer = value
End Set
End Property

Public Property dtsCommand() As String
Get
Return strDtsCommand
End Get
Set(ByVal value As String)
strDtsCommand = value
End Set
End Property

Public Property useProxy(Optional ByVal ProxyName As String = Nothing) As Boolean
Get
Return boolUseProxy
End Get
Set(ByVal value As Boolean)
boolUseProxy = value
strProxyName = ProxyName
If boolUseProxy = True And strProxyName = Nothing Then Throw New Exception("can not set use proxy to true, and proxy to Nothing")
End Set
End Property

Public Property username() As String
Get
Return strUsername
End Get
Set(ByVal value As String)
strUsername = value
End Set
End Property

Public Property password() As String
Get
Return strPassword
End Get
Set(ByVal value As String)
strPassword = value
End Set
End Property

Public ReadOnly Property iscomplete() As Boolean
Get
Return boolisfinished
End Get
End Property

Public ReadOnly Property jobStatus() As String
Get
Return strjobStatus
End Get
End Property

#End Region

#Region "Public Methods"

Public Function CreateJob() As Boolean
Try
'create a new sqlserver object

serverConn.ServerInstance = strSqlServer
serverConn.LoginSecure = boolSecureConnection
serverConn.Login = strUsername 'these should be moved over to the config file
serverConn.Password = strPassword 'these should be moved over to the config file
'create a connection to the server

svr = New Server(serverConn)
'set the jobserver
js = svr.JobServer
'create a unique job
Dim jobName As String = "dtexecRemote_temp_job_" + Guid.NewGuid.ToString
'create a new job catagory
Dim jc As JobCategory = New JobCategory(js, "dtexecRemote")
'set the job type to local
jc.CategoryType = CategoryType.LocalJob
'refresh the job category to see if it exists
jc.Refresh()
If Not jc.State = SqlSmoState.Existing Then
jc.Create()
End If
'create a new job
jb = New Job(js, jobName)
'set the category
jb.Category = jc.Name
'create the job
jb.Create()
'add the ssis goodies next
jb.ApplyToTargetServer(svr.Name)
'create a jobstep pointing to the package
Dim JobStep As JobStep = New JobStep(jb, "run package")
'add the DTS command to the jobstep
JobStep.Command = strDtsCommand
JobStep.ProxyName = strProxyName

'tell the agent to run an ssis job and actions to take
JobStep.SubSystem = AgentSubSystem.Ssis
JobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
JobStep.OnFailAction = StepCompletionAction.QuitWithFailure

'create the job
JobStep.Create()
jobCreated = True
Return True

Catch ex As Exception
jobCreated = False
Trace.Write(ex.Message)
Return False
End Try

End Function

Public Sub RunJob()

jb.Start()

While (jb.CurrentRunStatus = JobExecutionStatus.Executing)
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
End While

Do Until Not jb.LastRunOutcome = CompletionResult.InProgress And Not jb.LastRunOutcome = CompletionResult.Unknown
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
Loop

Dim outcome As CompletionResult = jb.LastRunOutcome

boolisfinished = True
strjobStatus = outcome.ToString

End Sub

Public Function ClearJob() As Boolean

Try

If jb.LastRunOutcome = CompletionResult.Succeeded Then
jb.Drop()
Return True
Else
Return False
End If
Catch ex As Exception
Trace.Write(ex.Message)
Return False
End Try

End Function

#End Region