0009 Running ETL workflows from third party software
A practical Advanced ETL Processor example for building, testing, and adapting one automation workflow.
Use this example to build one working automation package, test it safely, and then adapt it to your own files, folders, or data rules. Start small. Automation is helpful; automated mistakes are just faster mistakes.
This Example Demonstrates how to run ETL processes from the third-party software
Quite often our users want to trigger package execution from their own software. This can be easily done by adding a record to the QUEUE table, We have created a number of helpers stored procedures for Oracle, SQL Server PostgreSQL and MySQL. (Download source code)
Here is an SQL Server example:
CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]
(
@ComputerName NVARCHAR(255) ='',
@LogDirectory NVARCHAR(255) ='',
@Platform NVARCHAR(255) ='',
@User NVARCHAR(255) ='',
@ObjectId Int,
@UseAgent Int = 1 -- 1 = true, 0 = false
)
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,
COMPUTER_NAME,
APPLICATION_EXECUTED_PLATFORM,
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,
@ComputerName
as computer_name,
@Platform
as application_executed_platform,
@User
as osuser
from objects_tree
where object_id=@ObjectID
COMMIT
END
GO
EXEC EXECUTE_OBJECT 'DBSLCOMPUTER','C:\Logs','Windows (32-bit)','John',126,1
Log Directory Location
To access options dialogue, click System Menu→ File→ Options:
Object ID
Related Advanced ETL Processor resources
For more automation examples, review the Advanced ETL Processor tutorials, read the WIKI, or download the Advanced ETL Processor Enterprise trial.