Running ETL Packages from Active Table Editor

Several of our customers have asked us recently how to run Advanced ETL Processor or Visual Importer ETL Package from Active Table Editor or any other application.

All of these customers use the agent for execution.

The agent reads the QUEUE table several times per minute if there is something to execute it runs it.

QUEUE table has a lot of fields and it is not very clear which values to use.

Here is the basic SQL Server stored procedure:

CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]
(
@LogDirectory NVARCHAR(255) ='',
  @ComputerToExecuteOn NVARCHAR(255) ='',
  @ObjectID Int,
  @UseAgent Int = 1,
  -- 1 = True, 0 = False
@ExecuteOnComputer Int = 1
)
AS
BEGIN
BEGIN TRAN
-- Calculating ID
DECLARE @ID Int
select @ID =max(ID)+1
from ID_GENERATOR
where id_type=2
update ID_GENERATOR
set ID=@ID
where id_type=2
INSERT INTO QUEUE
(QUEUE_ID,
  OBJECT_ID,
  OBJECT_TYPE,
  OBJECT_NAME,
  LOG_FILE,
  SUBMITED,
  STATUS,
  USE_AGENT,
  EXECUTE_ON_COMPUTER,
  COMPUTER_TO_EXECUTE_ON,
  COMPUTER_NAME,
  OSUSER )
SELECT @ID
as QUEUE_ID,
  OBJECT_ID,
  OBJECT_TYPE,
  NAME
as OBJECT_NAME,
  @LogDirectory+'\Package_'+Cast(@ID
as VARCHAR)+'.log'
as LOG_FILE,
  getdate()
as SUBMITED,
  'P'
as STATUS,
  @UseAgent
as USE_AGENT,
  @ExecuteOnComputer
as EXECUTE_ON_COMPUTER,
  @ComputerToExecuteOn
as COMPUTER_TO_EXECUTE_ON,
  @@SERVERNAME
as COMPUTER_NAME,
  user
as OSUSER
from objects_tree
where object_id=@ObjectID
COMMIT
END
GO