3

I'm trying to build a transformation in Kettle that gets FIELDS from a SQL Server Stored Procedure and inserts it in a MySql table.

The problem is that I can't find a way to get stored procedure "fields". I understand that Call DB Procedure task expects in/out params, and that's not my case, so I'm trying to use "Execute SQL Statements" with the following SQL:

exec credisfera.dbo.sp_insere_parcelas @dt_ref = '2016-05-03'

Is there a way to achieve this?

jfneis
  • 2,139
  • 18
  • 31
  • If you put the above statement into a `Table input` step, right click and select "Output Fields...", what do you get? PDI should just get the metadata of the SP automatically. It works for me. – Brian.D.Myers May 03 '16 at 17:15
  • Hey Brian, I ended inserting everything I needed in another table and then using a Table input to read from it. Now it looks so obvious. :) If you wish, please post it as an answer so I can give you the credits. – jfneis May 03 '16 at 21:17

1 Answers1

5

Simply put the exec statement in a Table input step. Upon execution (or "Output fields...", PDI will get the metadata from the JDBC driver.

Brian.D.Myers
  • 2,448
  • 2
  • 20
  • 17
  • 2
    However doing so will have some side effect - the procedure will be called each time pentaho will need fields metadata information in design time. If you do not want those side effects in your transformation consider using "Dynamic SQL Row" step - it allows you to provide a template SQL-request for metadata, so you can use something like SELECT '' as Field1, 0 as Field2 etc. and hardcode the fields list. – morincer May 04 '16 at 15:21
  • @morincer, you are right. Our case is a read-only procedure, so there is no problem. But good point anyway. – jfneis May 04 '16 at 21:04