Database integration properties

ConnectionString

The parameters used to connect to the source database.

Example

SQL: DataSource=ServerName;InitialCatalog=DatabaseName;IntegratedSecurity=SSPI

Oracle: User ID = XXX;Password = XXX; Data Source = XXX

DatabaseType

The type of database the workflow integration connects to.

SQL, Oracle

SelectCommand

The command used to select records from the database.

If a simple IsProcessed column is used on the table, the Select Command may look like:

Select * from Example1 where IsProcessed = 0

If you use a stored procedure, a common practice is to reuse the procedure for different integrations. Doing this requires that one or more parameters are passed in to differentiate the integration.

SelectCommandType

The type of Select command to use.

Text, StoredProcedure

SourceDatabaseParameters

The parameters that are used to query the source database.

If you want to pass in parameters to the source database, configure this property. You can add one or more parameters and for each parameter, specify the following:

• Name: The name of parameter to be passed in. When used in the select command, this is replaced by the value.

• Data Type: The data type of the parameter

• Size: The size used in the data type, only valid for string or numeric types, for example, nvarchar, and decimal.

• Value

The format for the SelectCommand to use parameters is “@[name]” where name is defined previously. The format for text and stored procedure is different:

• Text - An example Select Command where SampleLocation is the name of the parameter.

  Select * from Example1 where Location = @SampleLocation

• Stored Procedure

  sp_getExample2

  The sp_getExample2 stored procedure can have one or more parameters, but you do not supply these in the Select Command. Define these parameters in the Source Database Parameters, they require the same name as the parameter names for the stored procedure.

UpdateCommand

The command used to update or archive records in the database.

UpdateCommandType

The type of Update command to use

Text, StoredProcedure

UpdateDatabaseParameters

The parameters that are used during the update command. For more information, see Update Database parameters.

FieldMappings

The field mappings for each pair of source/destination fields.

Add the field mappings using the Mapping Collection Editor. Click Browse (...) to open.

For each pair, configure these properties:

• Source: Select one of the source fields that you defined previously in the Source Fields.

• Target: Select one of the destination fields that you defined previously in the DestinationFields.

  Important: The Data Type for a source field and destination field must match to create a successful mapping between them.

SourceFields

The set of fields that are provided by the integration source. These fields are the source of data. Add and configure each incoming source field using the Field Collection Editor. Click Browse (...) to open.

For each field, configure these parameters:

• Name: The exact name of the column returned in the Select Command.

• Allow Nulls: Determines whether to use the Default Value if a null is found in the source. A null is classified as an empty string, or more specifically, the value between the Separation Character trimmed of spaces being empty.

• Data Type:The data type of the field. If this parameter is set to DateTime, the only format accepted is “yyyy-MM-ddTHH:mm:ssZ” which is the ISO 8061 standard for date times. The value must be in UTC.

• Default Value: This parameter is applicable only when the Allow Nulls is False. It specifies the value to use when a null is encountered.

• Max Length: This is parameter is applicable only when the Data Type is String. It specifies the maximum length of the string.

• Schema Usage: Not applicable in this integration.

Collection of fields

DestinationFields

The set of fields to which AVEVA™ Production Management writes the data.

Use the Field Collection Editor to add field items for each receiving field. Click Browse (...) to open. Configure the parameters the same way that you configured the source fields.

A field of the same name must exist on that reporting point location. However, not every field in the reporting point must be configured to receive data. Destination fields are configured with ColumnName rather than field name.

Collection of fields

AllowModifyExistingRecords

Enables or disables the ability to update records with new arriving information.

True, False

FilterFields

The fields to identify the unique record row to update. This property appears only when AllowModifyExistingRecords is set to True.

Use the FilterField Collection Editor to select the fields for this property. Click Browse (...) to open the editor.

Collection of fields

Location

The location in Studio where the external data enters the AVEVA Production Management system.

Use the Select Location dialog box to choose the location in Studio that is the entry point for the external data. Click Browse (...) to open the dialog box.

Reporting point, or Material movement

TriggerExpression

Specifies the expression that determines when the variable information is collected. Execution occurs when the result of the expression changes from False to True.

It is common practice to point to a timer, 15 minutes or 1 hour.

An expression