VERIFIED SOLUTION i
X

Inserting metadata into SQL Server database for EngageOne Vault

UPDATED: September 27, 2018


Vault provides an automated method of uploading information to an ODBC-compliant (Open Data Base Connectivity) database product. It is used to automatically export metadata from job loads to an ODBC data source. This is normally performed shortly after new documents are added into the Vault.

The ODBC export function can upload the Vault information to any ODBC-compliant server, including Microsoft Access, MS-SQL Server, Oracle, Sybase, and other such products (please note this is only available in a Windows environment).

Configuring ODBC export requires several steps:

1. Enable the ODBC export component in the Vault load process. This is done by adding the following to the e2loaderd.ini file in the server directory:

[sql1]
enable=1

2. Enable ODBC export for each profile that will export data to the target ODBC database, to do this add the following to the profile:

[someprofile]
ExportEnable=1

3. Configure the parameters needed to connect to the data source. The source is typically a machine DSN.  Note: on 64-bit platforms you need to use the 32-bit ODBC Data Sources configuration tool to create or modify the data source.

(a) Using a data source name, user name and password:

[someprofile]
ExportSource=Vault (name of the database)
ExportUser=user (this is the user set up in the Security\Logins tab for the Vault database)
ExportPassword=password (this is the password setup in the Security\Logins tab for the Vault database)

(b) Using a connection string:

[someprofile]
ExportConnectionSting=DSN=Vault;Uid=user,Pwd=password
Consult your database documentation on the options in the connection string. You may also find information at http://www.connectionstrings.com/ useful.
Note: The connection string should not have any embedded newline characters.

4. Select the target table to insert records into.

[someprofile]
ExportTable=Documents (name of the table receiving the data)

5. Define the fields to export and their mapping in the target table. This table must already exist. Each field is specified by an ExportFieldN= line in the profile where N is a number in a contiguous range starting with 1. It consists of the target column name, the Vault document field name and the data type. The data type is 's' for strings, 'd' for dates and 'n' for numbers.

[someprofile]
ExportField1=Account,doc.account,s
ExportField2=Created,doc.date,d
ExportField3=Pages,doc.pages,n
ExportField4=File,int.file,s
ExportField5=Offset,int.pointer,s
ExportField6=Profile,int.profile,s
ExportField7=Resource,int.resource,s
Exportfield8=Type,doc.type,s
Exportfield9=Name,doc.name,s

Obviously it will be necessary to ensure that the table has been set up in SQL to receive the data, ensuring the column names and data types (of appropriate lengths) are correct.  The names of the columns (Account, Created, Pages and others) will come from the values in the journal for this index entry)

To set up a database and table to receive data:
1. Using SQL Management Studio, create a new database with any name, but ensure that the name matches the export source name defined, for example Vault
2. Right click on table-> select new\table which by default will be named Table_1, this can be changed when the table is saved-> Enter the column name for example “account Number”-> pick the type ” char”-> enter the length” 8000” (remember the column names need to match the ExportField parameters in profiles.ini) and save
3. In the Security\Logins section of the SQL instance, create a user, for example Vault, this user name must match that defined in the ExportUser parameter, and set a password, this must match the password defined in the ExportPassword parameter using SQL authentication, and set the default database to the one created in step 1.  In the Server Roles for this user, assign the role of sysadmin
4. In the User Mapping section, select the database set up in step 1, and ensure the Role Member has the db_owner and public checkboxes active

Automatic Export
During the load process, jobs are compressed, document metadata is built, and then they are indexed.

When a profile has ODBC enabled, you'll see a message in the e2loaderd log after the index operation that indicates that a request to export to ODBC has been triggered:
12:14:36 <sql1> sql.mark request, file [20120411-tryme-telco-statement]
12:14:36 <sql1> sql.mark returned, elapsed [1]

When the actual ODBC export runs, log messages similar to these are returned:
12:14:46 <sql1> exporting [docdata\20120411-tryme-telco-statement.drd] to [Vault] table [Documents]
0 10 20 30 40 50 60 70 80 90 100
| | | | | | | | | | |
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
12:14:47 <sql1> export complete, [500] added, [0] errors

Manual Export
The ODBC export of a particular loaded job can be manually triggered by creating a .sql flag file in the server's process directory and the base name of the flag file should be the name of the job file to export.
For example, if a document data file is named “20011111-tryme-telco-statement.drd”, it can be exported to the ODBC database by creating a flag file at the command line such as:

Server>echo x > process\20011111-tryme-telco-statement.sql

How to set up ODBC DSN
A Data Source Name (DSN) is the logical name that is used by Open Database Connectivity (ODBC) to refer to the drive and other information that is required to access data.

The name is used by Vault ODBC export for a connection to an ODBC data source, such as a Microsoft SQL Server database.

To set this name, use the ODBC tool in the Control Panel.

“SQL Server” has been used in this example but it is similar for other servers. Create a System DSN in Windows XP
1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab, and then click Add.
3. Click the database driver that corresponds with the database type to be connected to, and then click Finish.
4. Type the data source name. Make sure to choose an easily remembered name because this name will be referenced later.
5. In the option to determine how SQL server should verify the authentication of the login ID, select SQL authentication, and check the "connect to SQL server to obtain default settings" option, where the user credentials set up for the database are entered
5. Change the default database to connect to, which will be the database set up in SQL to store the metadata from Vault
6. Continue to click Next until Finish.
7. Click Finish
8. In the ODBC setup dialog, click Test Data Source for test the con  guration to ensure that the connection was successful.
9. Click OK to finish.

Downloads

  • No Downloads