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

Question

Based on the example here from MSDN, how could one provide SSIS package level variables if one were to invoke the package via "sp_start_job" procedure?

Here is the example 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 supply values to the variables inside the SSIS package named "RunSSISPackage"?

Answer 1

you could create a table to act like a queue, where you insert a row containing your parameters and then start the job. Within the TSQL that then runs, just select out the values and mark the queue row "C"ompleted or just delete it and be on your way.

EDIT

try looking at Running SSIS package programmatically which covers all of the following methods:

• Run package programmatically using SSIS Object Model
• Start DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages
• Use SQL Agent. You can configure an Agent job to run your package
• Use some other utility to start DTEXEC for you
• Create a custom application that will run the package

Answer 2

We change our variable values dynamically at run time through the use of a configuration table or a configuration file. We don't let users run our SSIS packages, but I suppose your job could have a step that first updates the config file and then runs the package.