Clever Geek Handbook

Using SQL Server Agent to Run a Remote SSIS Package Programmatically on a Server

As per the example here from MSDN , how can you provide SSIS package level variables if you called the package through the "sp_start_job" procedure?

Here's some sample code from MSDN:

Dim jobConnection As SqlConnection
Dim jobCommand As SqlCommand
Dim jobReturnValue As SqlParameter
Dim jobParameter As SqlParameter
Dim jobResult As Integer

jobConnection = New SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI")
jobCommand = New SqlCommand("sp_start_job", jobConnection)
jobCommand.CommandType = CommandType.StoredProcedure

jobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
jobReturnValue.Direction = ParameterDirection.ReturnValue
jobCommand.Parameters.Add(jobReturnValue)

jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
jobParameter.Direction = ParameterDirection.Input
jobCommand.Parameters.Add(jobParameter)
jobParameter.Value = "RunSSISPackage"

jobConnection.Open()
jobCommand.ExecuteNonQuery()
jobResult = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer)
jobConnection.Close()

Select Case jobResult
  Case 0
    Console.WriteLine("SQL Server Agent job, RunSISSPackage, started successfully.")
  Case Else
    Console.WriteLine("SQL Server Agent job, RunSISSPackage, failed to start.")
End Select
Console.Read()

How can I provide values ​​for variables inside an SSIS package named "RunSSISPackage"?

+2


source to share


2 answers


you can create a table that will act as a queue where you insert a row containing your parameters and then start the job. Inside TSQL, which is then run, just select the values ​​and mark the queue line "C" ompleted, or just remove it and be on your way.

EDIT



try the SSIS Software Package which covers all of the following methods:

  • Run a Package Programmatically Using the SSIS Object Model
  • Start the DTEXEC.EXE process. DTEXEC is a command line utility for executing SSIS packages.
  • Use SQL Agent. You can configure an agent job to run your package.
  • Use another utility to run DTEXEC for you.
  • Create your own application that will run the package
+2


source


We change our variable values ​​dynamically at runtime using a config table or config file. We do not allow users to run our SSIS packages, but I suppose your work might have a step that updates the config file first and then runs the package.



0


source






More articles:


All Articles