DriveWorks has the ability to export data directly to a SQL Server database as part of running a DriveWorks Project.
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).
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 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.
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.
In DriveWorks Administrator open the Project the Document is to be added to and go to Stage4: Output Rules then Documents - Data.
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 section below.
The SQL ServerCredentials dialog will launch. This requires the following pieces of information:
Build the rules for the data export in the document editor window.
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.
The options for the SQL Server Export can have rules applied once the initial connection has been set up.
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:
The rules for the export are edited by returning to Stage 4: Output Rules then Documents - Data.
The SQL Server connection Wizard can be reopened by:
The SQL Server Data Export Document is triggered in the Specification Flow by the Release Documents Task.