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) ='',
@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
SELECT @ID as QUEUE_ID,
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
EXEC [EXECUTE_OBJECT] 'C:\TEMP','DBSLCOMPUTER',126,1,0
The rest of the parameters is self-explanatory