Generally I am known on this website for bringing infrequent, but wise tips to as many developers as I can. *wink* *wink* Here is an attempt to ramp up my efforts to help educate people, convert as much C# code to VB, and create some real Microsoft development enthusiasts.
This post will cover, SQL Server Integrated Services (SSIS), and namely one question, with several requirements. You want to execute an SSIS package remotely, perhaps from an ASP.NET webpage? You have a need to do this adhoc, with multiple users. You need to set variables inside the package, hell why else would you go through all the hassle to run this on demand?!
Ok so this sounds like a big issue. First, this is not a beginners SSIS post. If you are looking for how to set custom parameters through execution of SSIS this is not the place to start. However that said, I will provide a brief overview of how it works.
SSIS allows you to use the DTEXEC command (a windows executable, and SQL server command) to execute SSIS packages stored in various locations (local, ssis package store). The following option in a command line allows you to set a variable at runtime.
/SET "\package.variables[VariableName]“;”Value”
By setting these commands at runtime, you just increased the power of your SSIS capabilties 10 fold. You can now user the power and localization of the SQL server to bear the grunt work of many data tasks, while you leave your application server’s resources left open for other user crunching. Now we are on the path for removing mindless data routines out of code, into a proper visual designer, and enchancing out application performance by offloading data routines to something that is dedicated to data!
Ok with that little bit of education out of the way lets continue! We’re assuming that you understand SSIS and SQL Server enough, to be able to Create an SSIS package, upload it to your server and execute it from a SQL Agent Job. This includes making a proxy. In addition to this, you will need a user that belongs to msdb, and can execute dts jobs. (dtsadmin)
Now for the code! This is converted, revamped from dtRemoteExec in C# found on codeplex. There were a few issues I found with this process in daily use. I have started to modify (it is by no means perfect) this code, which can be used in enterprise wide applications. Eventually I would like to have the code build the DTS Exec string for you, but this is still needed.
Here is a little explanation of how things work first! I almost forgot. You will need a DTS execution string like this.
"/SQL "\Maintenance Plans\SSISPACKAGE" /SERVER "b3studios" /USER username /PASSWORD password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET "\package.variables[ExportLocation]“;”C:\Export”
I prefer to store the package with in the SSIS Package store. HOWEVER!!! Do not use a /DTS for calling the package, which is the default when using SSIS Package Store. This will almost certinly fail, or produce sporatic results. Instead, I prefer to execute the package from the SQL Server, using the /SQL command, let me know your thoughts in the comments.
Ok wheew mouth full, and I should rewrite a lot of this! But I’m too busy to do so! Here is the code, dubbed RemoteSSIS, based on dtexecremote, and rewritten in VB.NET
Here are the calls you will use from your application.
Dim oRemote As New RemoteSSIS
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
If oRemote.iscomplete Then
Return oRemote.ClearJob()
End If
Here is the code you can add to any module in your project. Add the following references,
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlSever.SMO
Microsoft.SqlSever.SmoEnum
Microsoft.SqlServer.SqlEnum
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
End Class
I would love this. If I could send a message from Jabber to my Wordpress blog and have it published. Perhaps I need to investigate XMPP more. 