Resolve Workflows with Database Activity

When a workflow contains a database activity, the Database Activity grid displays the auto generated SQL scripts for the database activity. The SQL script can be a query, a function, or a procedure. If the workflow contains multiple database activities, multiple scripts are generated based on the associated artifact (for example, a database connection) of the database activity. While installing the package in the target environment, the SQL script is run first and then the package is installed.

While adding the Workflow with Database Activity to a package template, if you select the Include the automatically generated SQL Script option as Yes, the automatically generated SQL script is included in the package template. If you select the Include the automatically generated SQL Script option as No, the automatically generated SQL script is not included in the package template.

Use the Install tab or the Update tab in the Settings form to set the configurations for the Database Activity.

The Database Activity grid contains the following fields:

• Activity Name: Displays the database activity name.

• SQL script: Displays the auto-generated SQL script for the particular database activity.

  The SQL script can be a query, a function, or a procedure. You can append any additional scripts at the end of the auto-generated SQL script. The SQL script in the query window can be packaged from one SQL server machine and can be installed in another SQL server machine only.

  Note: The query generated using the Expression Editor is not considered while generating the SQL Script automatically.

• Validate SQL script: Validates the script for the usage of the restricted SQL commands, but does not validate syntax of the SQL query in the script. Ensure to enter error free SQL script.

  The following SQL commands are restricted:

  • Delete

  • Truncate

  • Drop

  • Update

  • Create User

  • Grant

  • Revoke

  • Execute

    If the database activity contains a query, the script will be generated for the tables in the query. Parent tables and child tables are not considered. Such a query will contain the following information:

  • Table structure of the tables in the query, with field name, data types, and their order

  • Keys - primary key and foreign key

  • Constraints - default or check, and so on

  • Index

    If the database activity contains a procedure, a script is generated for the procedure. However, objects such as tables or views used by the procedure are not generated. All such objects must be available in the target environment before package installation.

    If the database activity contains a function, a script is generated for the procedure. If the function contains parameters you should specify the exact parameters.

    The following points are not considered while generating the SQL script:

  • User defined types

  • Table partitioning

  • Views

  • Triggers

  • Functions

  • Synonyms

  • Parent and child tables

  • Query against views and functions

    If you need any of the unsupported objects, then you can choose to do one of the following:

  • Write and run the script manually in SQL Server

  • Write and append the script to the SQL script text area

    The auto-generated script contains a pre-condition statement which checks for the existing tables in the target environment. If the table exists in the target environment, the SQL script does not run. If the table does not exist in the target environment, the SQL script runs and creates the table, and adds the keys, index, and constraints as defined in the source environment.

    Ensure that the object names used in the source repository does not conflict with the destination repository. If any of the objects (name of a table, key, constraint, or index) exists in the target environment, then the script execution will fail. This will be recorded in Package History page after the package installation.

    As the script is auto-generated, we recommend not to modify the script. If you want to add your own piece of code sample to the script, you can either add it at the start of the script or append it to the end of the script. Do not add or edit the existing script.

    Generating scripts for Oracle database is not supported. The SQL script text box will not be visible when the database in source environment is Oracle.

    Note:
    - If the SQL script is executed successfully, then the following message appears in the Package History dialog box:
    The SQL script is executed successfully.
    
    - If the SQL script execution fails, then the following message appears:
    The SQL script execution failed.
    
    - If you click the message, the Message Details window appears, where you can view the message details.

    See Also: SQL Script Generation and Execution