Integration Task: Database Push
Using the Database Push Task
The Database Push task allows you as a Process Administrator to design processes that will communicate directly with other relational database systems in your organization while a process is executing.
This task allows the process to push the data gathered within an Integrify process directly into a table in a database of your definition (i.e. HR/Payroll, a data warehouse or any relational database (i.e. PeopleSoft, SAP, etc). For example, customers can eliminate manual data entry between systems by extracting data from an activity in an Integrify process and update their HR/Payroll system.
It is most commonly used to gather one to several pieces of information within the scope of a given request as it is executing and insert or update data to a remote database.
For instance, perhaps during a sales process after a lead has been qualified as a potential sales candidate you would like to gather the estimated sales and close date and insert those values into a custom table for report purposes:
The highlighted task in the flow above represents the Database Push task that will execute after the Sales Follow-Up form task is completed above it. When it executes it Integrify will execute a SQL statement and insert data into the Custom DB server somewhere on the network.
Configuring the Database Push task
Once a Database Push task has been added to your Process Flow, right click on the task under configuration, choose the Configure Task option:
The Connection tab will display where you will have to configure how you will be inserting data (SQL Statement or Stored Procedure), what database server you will connect to, and the type of database provider you will need to use.
In this particular scenario, an INSERT type SQL statement has been added to the Query tab, and it will use specific Query Parameters that are mapped to data elements within the request. Additionally, new database connection string has been added so Integrify will connect to a remote database to perform the INSERT at run-time.
Query Parameters
The Query Parameters tab allows you to capture data from the request that is executing and use it within the SQL Statement that will retrieve your list of users who will be assigned as recipients to a task.
In the SQL statement below, you will notice a @first_name, @last_name, and @email syntax that has been included in the SQL Statement for this Database Push configuration:
At run-time, Integrify will use a specified value from within the request and insert it into the SQL statement prior to execution. In this case, it will be the value of the unique First Name, the Last Name and the Email from a form within the process.
To add a parameter to your Database Push configuration, select the Query Parameters tab and then the Add Parameter option:
Similar to creating a transition rule, the parameter dialog will allow you to define the name of the parameter and the Source (Data, Requester, Client, etc). Depending on the Source you select, the following lists will be dynamic.
In this particular case, the Source is a data-based information from the request (the First Name value from within the New Applicant Form task). When the Database Push executes, Integrify will retrieve the First Name and replace the @first_name parameter name with the value.
Note: You may use multiple parameters in a SQL Query or the same parameter multiple times.
Calling a Stored Procedure
It is possible to use a stored procedure to perform you database actions as needed using the Database Push task. The primary difference is the initial SQL construct and the Command Type task option:
In this case, we are calling a SQL Server stored procedure called spInsertSalesForecast and passing in three parameters. Also notice that the Command Type has been changed to Stored Procedure.
SQL Server Update (7/15/2016): It was discovered that attempts to call a stored procedure using the configuration illustrated above results in a misleading error about the procedure not being found. It may be related to the parameter definition order referenced in the Oracle comment below. To address this issue, the quickest and simplest solution is to configure the Command Type as Text and prefix the procedure name with the T-SQL command EXEC.
Important! If you are using an Oracle stored procedure, take care on order of the parameters. If you were calling a procedure like:
spInsertSalesForecast @request_id, @sales_forecast, @close_date
Then you must create the parameters in that order in the Query Parameters tab (i.e. @request_id is at the top of the list, then @sales_forecast, etc). This is due to a constraint with the Oracle Provider. SQL Server does not have the same requirements.