Resources about Load/Run SSIS packages using .NET Remoting.

Resources about Load/Run SSIS packages using .NET Remoting.

• salvoc

  Hi,
  thankx for the link...that was my starting point when I tryed to load and run packages using SQL Server Agent ...
  
  The example use the sp_start_job which input is only the name of the Pachage..If I check the signature of the SP there is the possibility to have other possible input values but not an input parameter that takes my "dinamic" variables ...
  (as you have understood as VB developer my SQL skills are not so good)
  The next week I will try your hint and I will let you know!
  Thank you very very much!

  Marina B.

  
  

• Mobile Rob

  mhhh..
  your answer is clear but it doesn't completly clarify my doubt.

  My development machine has installed all I need but the exe that I'm authoring will be installed on the client that doesn't has any SQL Server 2005 feature installed and will run the DTSX remotly.

  So I was asking to myself: I'm working in a remote way or I'm working on a local way

  I want to avoid to have surprises during the test phase on the client ...
  
  So at the end to create the remotable object that load and run the package using the SSIS APIs and to have the client that instanciate this remotable object is enought to garantee that I'm working with SSIS on a remote way
  
  Thank you very much!

  Marina B.

  
  

• nmullane

  The SSIS server does not expose any methods to run the packages. It has ability to monitor them, and the API to list running packages and request a package termination. It also has some storage API, but no execution functionality.
  
  

• Prof_CRM

  You cannot set a variable by calling sp_start_job, quite true, but plenty of alternatives.

  How about creating the job command each time you want to run the package, setting any variables as you Go. If you use the CmdExec subsystem, then the dtexec /SET switch can set variables.

  You could insert values into a table, in fact a SSSIS confirguration table, then use a SQL Server based configuration to read the values into your package and assign as required. You could also insert the values into any table and retrieve them through the Execute SQL Task and assign to variables.

  Any of those work

  
  
  

• Nicolas Coderre MSFT

  You can create a job and start it through T-SQL, just by issuing the relevent commands, just like you would build scripts to setup jobs on your servers for any other job.

  To start a job via T-SQL, you can use the stored procedure sp_start_job.

  The big text area on teh CmdExec job step UI is indeed for your DTEXEC command.

  The SSIS job step subsystem is fine, but I dislike it myself as it does not always give you sufficient errors information. Link somewhere to wiki post on it.

  This script will create a job. Change the obvious bits, names etc, and commands, or just run it, then change stuff in the UI and then script it off again.

  USE [msdb]

  GO

  BEGIN TRANSACTION

  DECLARE @ReturnCode INT

  SELECT @ReturnCode = 0

  DECLARE @jobId BINARY(16)

  EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JobnameBLAHBLAH',

  @enabled=1,

  @notify_level_eventlog=0,

  @notify_level_email=0,

  @notify_level_netsend=0,

  @notify_level_page=0,

  @delete_level=0,

  @description=N'No description available.',

  @category_name=N'[Uncategorized (Local)]',

  @owner_login_name=N'Username', @job_id = @jobId OUTPUT

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  /****** Object: Step [StepnameBLAHBLAH] Script Date: 05/30/2006 16:35:22 ******/

  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StepnameBLAHBLAH',

  @step_id=1,

  @cmdexec_success_code=0,

  @on_success_action=1,

  @on_success_step_id=0,

  @on_fail_action=2,

  @on_fail_step_id=0,

  @retry_attempts=0,

  @retry_interval=0,

  @os_run_priority=0, @subsystem=N'CmdExec',

  @command=N'DTEXEC Blah Blah Goes here',

  @output_file_name=N'C:\Set This Output File Good Logging.txt',

  @flags=0

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  COMMIT TRANSACTION

  GOTO EndSave

  QuitWithRollback:

  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

  EndSave:

  
  
  

• johnny1899

  I found the time and of course I had some problem to understand very well your answer above all the first proposal:
  " How about creating the job command each time ....etc..etc.."
  
  When you say job command you mean "sp_start_job"

  I started to play with the SQL Server Agent...
  I defined a new step ... first I added a step type: Operating System(cmdexec) and automatically a Command is created in the big textarea
  The dtexec /set you speak must be added in this textarea
  
  I have seen that there is also the type: SQL Server Integrated Services Package with a lot of tabs ..
  could you explain a possible scenario for this step.
  If it takes too long you could send me link that contains usefull resources..
  
  I'm quite new in SS2005 and I have some problem to find all the info I need.
  thank you
  Marina B.
  

  
  

  
  

• Nguyen Dai Duong

  I'm going to try the code you have written ...

  I'm confident in the code but my goaI is to run a package remotly and to set package's variables from the client application.
  
  If I define a job as you did in the code you wrote how can I pass those variables to the job's step
  
  Thank you very much
  
  Marina B.

  
  

  
  

• Durban

  The T-SQL is just a sample of how to create a job. I would expect your calling application to build something similar on the fly, then issue it via ADO.NET against the server. This would create a job which you can then start, again use ADO.NET and call sp_start_job. This is just a tempoary job really, and can be removed one complete. You do not want a permanent job as the "variables" may change each time. By building and issuing the create job commands from your application you have complete control over the command and so can include /SET options in the DTEXEC command as you see fit.

  There is an option to delete a job on completion which may be of use, look up the procedures used in Books Online for more information.

  
  
  

• Gene vantreese

  Ok...
  
  I think that is more clear now...

  I read about the possibility to use SQL Server Agent but I think that the main limitation is that is not possible to set package's variables.

  Isn't

  thankx
  Marina B.

  
  

• Shamez

  Yes, you can probably use Remoting, but in this case you need to write an application that will expose remoting end point on the server machine. The SSIS service does not expose any remoting end points for clients to use. So it will be your app that exposes Remoting Object, not SSIS service. I think it will work, but we have not tested this scenario.
  
  The common way to run packages remotely is via Agent. You create an Agent job, without a schedule. Then you start this job on demand from a remote machine.
  
  

• mdeep

  I just found this BOL topic which illustrates some of what I am trying to convery, starting a job through ADO.Net. It really is terrible how MS keep adding all this usefull stuff to BOL, it is very hard to keep up!

  Loading and Running a Package Programmatically on the Server
  (http://msdn2.microsoft.com/en-us/ms403355(SQL.90).aspx)
  

  
  
  

• George3

  uuhhh ....

  I need a little bit of time to try them...

  Thank you very much for the advice and I will come back with a feedback ASAP.
  Marina B.

  
  
• Resources about Load/Run SSIS packages using .NET Remoting.