1

I have scenarios like where I do not have select access on to the whole table or table's to one of my Source DB, the only way I could get access required columns and bring the data into the Pentaho layer is by stored procedure.

Below is the sample stored procedure I wrote, I could get the result set in DB after doing EXEC ABC_KLM_LOAD but not sure how to call this stored procedure into Pentaho Data Integration to continue further validations via steps in PDI 

USE PDI_TEST
GO 

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE OR ALTER PROCEDURE ABC_KLM_LOAD 
AS 
BEGIN  
    SELECT * 
    FROM [ XYZ_01] 
END 
GO
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Koushik pandinya
  • 11
  • 1
  • 2

1 Answers1

0

Here is an example calling a stored procedure on an MS sql server. You need to use the step 'Table input' in a pdi transformation, then write something like;

USE xxx_ReportService

DECLARE @DateFrom datetime, @DateTo datetime
SELECT @DateTo = CONVERT(date, DATEADD(DAY, -0, CURRENT_TIMESTAMP))
SELECT @DateFrom = DATEADD(DAY, -1, @DateTo)

EXEC [ZZZZ].[sp_SQLReport_GGG_SpecialReport]
        @GeneralServiceDB = 'xxx_GeneralService',
        @PaymentServiceDB = 'xxx_PaymentService',
        @DateFrom = @DateFrom,
        @DateTo = @DateTo
RAMK
  • 11
  • 2
  • Appreciate your time in responding to it @RAMK , but my use case is to call the stored procedure in Pentaho Data Integration (ETL Tool) , with input & ouput params , since i do not have direct select on to the table , we are going through via Stored procedure. – Koushik pandinya Jan 10 '20 at 15:12
  • I believe this is still what you need. The table input step in pentaho data integration (pdi) allows you to run sql, or a stored procedure; https://imgur.com/dLzJORp – RAMK Jan 11 '20 at 16:13