Execute Stored Procedure from BODS and then start data extraction from SQL Table

Here, I will try to explain the simplest way to execute SQL Stored Procedure from BODS and then continuously start data extraction.

Below the step by step to execute stored procedure and then start data extraction.

1. Create batch job and add Script and Workflow. Script will be used to execute stored procedure.

2. Function sql() will be used to execute SQL stored procedure, call this function in Script as like below

Syntax: sql (datastore, sql_command)

DataStore: A string containing the name of the datastore where the tables involved in the SQL operation reside. This name is the name you specified when you created the datastore in Data Services. Include this string in single quotation marks.

SQL_Command: A string containing the text of the SQL command to execute. This string must be enclosed in single quotation marks (‘). If the string contains quoted values, the internal quotation marks must be single quotation marks preceded by the escape character, backslash (\).

Example :

DataStore Name: SQL_FI_SRC

Stored Procedure Name: SP_Fill_FITable

If SQL Stored Procedure name as dynamic input, then

Create Global variable under Batch Job and use the same to pass SQL stored procedure name while executing the job, below the example

Global Variable:  $Exe_Statement

DataStore Name: SQL_FI_SRC

Create Global variable under Batch Job and use the same as stored procedure parameter and pass the value to variable while executing the job, below the example

Global Variable:  $CalMonth

DataStore Name: SQL_FI_SRC

Stored Procedure Name: SP_Fill_FITable

Required Parameter : @CalMonth , @Type

Assume @calmonth is a dynamic and @Type is default value then use the below script

Note: Since the script will be generating on specified datastore object, we cannot provide invalid datastore or variable asdatastore.

3. Above mentioned steps will execute the stored procedure and load the value into source table. Now the source table is ready for extraction.

4. Create dataflow to perform data extraction from source table and add under workflow as like below,

4. Execute the Batch Job, Script will execute the stored procedure and load the value to the source table after that dataflow  will perform the data extraction from source to target table .

Execution status will be available in “Job execution status Log”.