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
Execution example:
EXEC [EXECUTE_OBJECT] 'C:\TEMP','DBSLCOMPUTER',126,1,0
Parameters:
Log Directory:
Object ID:
The rest of the parameters is self-explanatory
Active Table Editor Execute Button
