PivotDatabase 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 on updating the Database parameters, see Update PivotDatabase 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.

SourceCulture

The culture associated with the source data in the database.

Use this setting when the data in the File is from a different culture to the one used by the server.

Any supported regional setting.

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 their corresponding parameters. For detailed information on the parameters, see SourceFields parameters.

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

AllowModifyExistingRecord

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.

Collection of fields

Location

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

Reporting point, or Material movement

Grouping Columns

The names of one or more AdaptorFields that act as the key to identify a set of records.

This property defines columns returned by the Select Command that are used to pivot the data. This is essentially the primary key when it is converted into a flat table.

PassedThroughColumns

The names of one or more AdapterFields that are passed unchanged through to the pivoted data.

This property defines columns returned by the Select Command that are included as columns in the flat table, but only contains the last value returned.

PivotColumn

The source AdapterFields that contain the values that become columns in the pivoted data.

This property defines which column returned by the Select Command holds the name of the column after it is pivoted.

PivotedValueColumn

The source AdapterFields that contains the values that are placed into the columns the pivot creates.

This property defines which column returned by the Select Command holds the value of the column after it is pivoted.