Monday, 26 February 2007

Calling a DB procedure within PDI/Kettle

Hi all,

Today, I want to share an interesting trick to run a DB procedure in PDI/Kettle.
But anyway, where's the trick, since there is a dedicated transformation to run such procedure ?
I discovered the following : when trying to run a DB procedure with PDI/Kettle using this dedicated job, nothing happens, the procedure does not run and no error is logged.
The answer is : first, you need to call a "Row generation" transformation and link it to your "Call DB Procedure" transformation.

But hey, don't forget to write "1" in the Limit field for the "Call DB Procedure", otherwise the procedure will run more than once !!!
I don't know if this is a real issue, but anyway who cares ? The job is finally done !!

Here is the snapshot.


aseno said...

I am trying to run a stored procedure that has 2 arguments/parameters. Do you know where/how to specify the args in the DB Procedure step? BTW thanks for sharing the trick!

Vincent Teyssier said...

Hi Aseno,
You can add parameters when editing the DB procedure step.
Look on bottom of the edit window, you will see a parameter list with name, direction and type.
Hope this is clear for you, otherwise drop me an email : vteyssier at decisionsystems-studio dot fr

Filipe Fumaux said...

It works a lot to me. Thanks

Anonymous said...

hai , can u please send some sample transformation to my mail. please,
mail id-praveenkumar.slm@gmail.com

CesarS said...

HI I did that... I think its nice but!!!!!!! how I can get the value result then to process the Call BD Procedure... I mean Later of that how I can get it???!!

This is my Procedure:

declare @string varchar
set @string = ( select cast(isnull(max(Fecha),'2016-08-31') as datetime) from Destino )
select @string as fecha

I need to pass @string to next step transformation..!!!!!!!!