Home Search

DriveWorks Pro 22
SQL Server Data Export

Send Feedback

SQL Server Data Export

DriveWorks has the ability to export data directly to a SQL Server database as part of running a DriveWorks Project.

Timeout

This Document is run by the Release Documents Task in the Specification Flow.

The Release Document Task will try, for 15 seconds, to export the SQL Server Data Export Document.

If the target database cannot be reached within this time, or the export takes longer the timeout period, the export will fail.

If you have a SQL Server or SQL Server Express database a direct connection for the export can be established.

Using the native SQL Server connection does away with the need to set up system DSN's to each database the export relies on. Each machine that the DriveWorks implementation can be specified from requires access to the SQL Server that hosts the database used.

Each SQL Server Data Export in the Document List can export data to a single table in a single database. By creating multiple SQL Server Data Exports, it is possible to export data to any number of tables in any number of databases.

Each SQL Server Data Export consists of one or more rows to be exported into the table in the target database.

Each row in the data export will be added to the table, or update an existing row in the table, or be ignored. The behavior for each row is controlled by Control Fields (see Before you Begin below).

Ensure that you are not bypassing the business logic of the application (for example an ERP system) by adding or updating data in the applications database tables. If in doubt, contact the application vendor or database administrator.

Before You Begin

Before attempting to export data to any database familiarize yourself with the database the data will be exported to. Pay particular attention to the data types associated to each column in the data table.

Data Types

Data types designate the type of data that will be stored within the field. This can take several different forms which includes (but is not limited to) text strings, numbers and dates.

It is important to note the data type of each column in the table you are exporting to.

The result of the rule, for each field of the data export, will need to match the data type the database is expecting.

Examples

When the Data Type is Float a numeric value must be the result of that column in the Data Export Document.

When the Data Type is Text a textual value must be the result of that column in the Data Export Document.

To Add a SQL Server Data Export:

In DriveWorks Administrator open the Project the Document is to be added to and go to Stage4: Output Rules then Documents - Data.

  1. Click the Add button on the command bar.
  2. Choose the  SQL Server Data Export document from the list on the Create Document wizard.
  3. Give the new Document a name.
  4. Click Next and then follow the steps below: 

Add the SQL Server Data Export Document

To use native SQL Server connection when using the Data Export Document:

Once you have completed the wizard to set up the connection, rules can be applied for the Path, Database and Authentication.

See To Edit a SQL Server Data Export below.

  1. Select SQL Server Data Export from the new document wizard.

    Enter a name for the document.

    Click Next.

  2. Select the SQL Server that hosts the database from the Find Server dialog.

    Click Next

    The SQL ServerCredentials dialog will launch. This requires the following pieces of information:

    • Authentication
      • Select Windows Authentication if the SQL database is configured to accept the login credentials from the machine the connection will be made from.
      • Select SQL Server Authentication to enter credentials that have been configured in the SQL server for a general connection.
    • User Name - Only required for SQl Server Authentication
    • Password - Only required for SQl Server Authentication

    Once the Authentication method has been selected click Log-on to connect to the SQL server.

  3. Select the database to connect to.

    Click Next

  4. Select the table in the database to export the data into.

    Click Next

  5. Select any fields that are to be the Control fields for the export.

    Control Fields control whether a row is inserted, updated, or ignored:

    • If the values in the control fields of a row do not match an existing row in the database then DriveWorks will insert a new row.
    • If the values in the control fields of a row match an existing row in the database then DriveWorks will update the existing row.
    • If any of the values in the control fields of a row are blank, then DriveWorks will ignore the row.

    Click Next

  6. Select any common fields that will have common rules applied for multiple row insertion.

    Common Fields can make it easier to export data to certain types of systems, for example PDM and ERP databases where oftentimes multiple rows will need to be exported, but certain columns always have the same value in each row. For example an IsSubmitted column might always contain the value 0.

    For these columns, you may only want to build one rule, instead of a separate rule for each row. To do this, mark the column as a Common Field, and DriveWorks will automatically use the same rule for the column in all rows.

    Click Finish to open the document editor

Build Rules for the Data to be Exported

Build the rules for the data export in the document editor window.

  1. Click Add Row to add a new row for the export.
  2. Click in a cell of the required column of the row number just inserted
  3. Click Build Rule to launch the rule builder for the selected filed
  4. Repeat steps 2 and 3 for each filed to build a row for.

Multiple rows can be exported by repeating steps 1 to 4 above.

By using Specification Flow it is possible to gain even more control over when the export takes place.

Each user that uses DriveWorks will need access to the database. This means that each machine the project can be specified from must see the server the SQL database resides on. Also the user account used to login to each machine will require permissions to write into the database.

To Edit a SQL Server Data Export:

Edit Options

The options for the SQL Server Export can have rules applied once the initial connection has been set up.

This is useful for managing separate Development and Production environments.

For example you will not want specifications made for testing purposes populating a production database.

Rules can be applied that connect to a testing database when using the Development environment, and then connect to the production database when using the Production environment.

From the Documents - Data task:

  1. Select the Document to edit from the Documents List.
  2. Click Edit from the command bar.
  3. From the Options section choose the SQL Option to build a rule for.

  4. Click Build at the end of the option field.
  5. Enter the Rule in the Rule Builder and click OK when done.

Edit Cell Rules

The rules for the export are edited by returning to Stage 4: Output Rules then Documents - Data.

  1. Select the Data Export document to edit
  2. Click Edit from the DriveWorks command bar
    • To add a row click the Add Row button on the command bar, and the new row will appear at the bottom of the table. To Insert a row select the location where you would like to insert the row then right click Insert Row. This will add a row above the selected cell.
    • To build a rule for a cell select the cell and then click the Build button on the command bar. If a column has been setup as a Common Field then the rule will only need to be entered once for it to apply to all rows.
    • To delete a row select the row number to highlight the entire row then right click Remove Row.

Cell Rule Examples

RuleDescription
NewCustomerReturnWill export the value of the control NewCustomer to the database.
"<<DWNull>>"Will export a NULL value to the database.

The SQL Server Data Export Document is triggered in the Specification Flow by the Release Documents Task.