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:
ETL log directory

Object ID

 Getting ETL Object ID

Please contact us if you need help with transforming the data

Visit ETL Tools Forum