Writing data using other OLE DB providers

You are not restricted to using the UNICOM Intelligence Data Model to write the data--you can write data using any suitable OLE DB provider that is installed on your system. Using a non-UNICOM Intelligence Data Model OLE DB provider to write the case data differs from using the UNICOM Intelligence Data Model to write the case data in several ways:

No output metadata is created, because it is not relevant to non-UNICOM Intelligence Data Model data formats.

Non-Data Model providers do not provide built-in support for categorical data and all categorical values are converted into text. By default the category values are exported as the unique numeric values that are known as MDM mapped values. This means the single response data in the output data source is in the form {34} where 34 is the MDM mapped value of the category that was chosen and multiple response data is in the form {34, 38, 42}. However, provided you are using an input metadata source, by setting the MR Init Category Names connection property to 1, the category names are exported instead. This means that single response and multiple response data is in the form {female} and {dinosaurs, fossils, evolution}, respectively and this is generally easier to interpret.

Some Data Model variable names contain characters that are not supported by some OLE DB providers. For example, the names that the UNICOM Intelligence Data Model generates for variables that are part of grids and loops typically contain periods . and brackets [ ], which are not supported by some OLE DB providers. UNICOM Intelligence Professional therefore replaces periods with the underscore character _ and brackets with parentheses ( ), which are generally supported. For example, Respondent.Serial becomes Respondent_Serial and CHILDHHZ[{YEARS_0_5}].Questi1 becomes CHILDHHZ({YEARS_0_5})_Questi1. However, these characters are supported by SQL Server and so the names are unchanged when you export to SQL Server using the Microsoft OLE DB Provider for SQL Server.

When you look at the exported data, you might find that the columns are in an unexpected order. This is because the sorting of the columns is controlled by the OLE DB provider you are using and the sorting rules vary from provider to provider.

In the OutputDataSource section, you must specify the name of the table to which you want to write the data. The table will be created if it does not exist already. You can append data to an existing table only if the provider you are using supports this operation and the structure of the data you are transferring is identical to (or a subset of) the data in the existing table. For example, if the existing table contains the variables age, gender, and income, exports that contain the variables age and gender, or age, gender, and income should succeed, provided the variables are of the same type and the provider you are using supports this type of operation. However, an export of variables age, gender, income, and occupation would fail.

The Provider tab in the Data Link dialog lists all of the OLE DB providers that are installed. (You can open the Data Link dialog in the Normal tab of the WinDMSRunWindow: see WinDMSRun window: Normal tab.)

Each provider has different requirements for the connection string. If you select a provider on the Provider tab in the Data Link dialog, and then click Next, the Connection tab shows the main connection properties for that provider.

For further information, see documentation about the provider.

The following example uses the Microsoft ACE OLE DB Provider to transfer a subset of the Short Drinks sample to an Access database. Before you run this sample, you need to create a database called DMSTransfers.mdb and make sure that it does not already contain a table called short_drinks.

InputDatasource(myInputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Initial Catalog=C:\Program Files\UNICOM\Intelligence\
        <version>\DDL\Data\Mdd\short_drinks.mdd; _
    Location="Provider=MSOLEDBSQL.1; _
      Integrated Security=SSPI; _
      Persist Security Info=False; _
      Initial Catalog=short_drinks; _
      Data Source=LocalHost"; _
      MR Init Category Names=1; _
      MR Init Project=short_drinks"
  SelectQuery = "SELECT Respondent.Serial, gender, age, income,
      occup, hhsize FROM vdata WHERE DataCollection.
MetadataVersionNumber = ’5’"
End InputDatasource

OutputDatasource(MSAccess)
  ConnectionString = "Provider=Microsoft.Jet.ACE.12.0; _
    Data Source=C:\Program Files\UNICOM\Intelligence\<version>
        \DDL\Output\DMSTransfers.mdb; _
    Persist Security Info=False"
  TableOutputName = "short_drinks"
End OutputDatasource

The MR Init Category Names connection property has been set to 1 in the InputDataSource section. This means that the category names will be exported rather than the numeric category values. You must not use this setting if the DMS file contains another OutputDataSource section that writes data using the UNICOM Intelligence OLE DB Provider.

The following example shows an OutputDataSource that uses the Microsoft OLE DB Provider for ODBC Drivers to transfer data to an Excel file. This OutputDataSource section assumes that an empty Excel file called MSExcelTransferToFromRDB.xls already exists in the output folder.

OutputDatasource(MSExcel)
  ConnectionString = "Provider=MSDASQL.1; _
    Persist Security Info=False; _
    Data Source=Excel Files; _
    Initial Catalog=C:\Program Files\UNICOM\Intelligence\
      <version>\DDL\Output\MSExcelTransferToFromRDB.xls"
  TableOutputName = "Sheet1"
End OutputDatasource

You can create the empty Excel file using the following code in the OnBeforeJobStarts Event section.

Event(OnBeforeJobStart, "Do some file and folder management")
  Dim MyExcelApp, MyExcelBook, MyExcelSheet
  ’Set up my Excel document
  Set MyExcelApp = CreateObject("Excel.Application")
  MyExcelBook.SaveAs("C:\Program Files\UNICOM\Intelligence\
      <version>\DDL\Output\MSExcelTransferToFromRDB.xls")
  myExcelApp.Quit()
End Event

Microsoft has limitation of 255 columns when using the OleDB provider to export to Excel.

The following example shows an OutputDataSource that uses the Microsoft OLE DB Provider for SQL Server to transfer data to a SQL Server database. This transfers the data to a single table that matches the UNICOM Intelligence Data Model VDATA virtual table and does not use the schema and format used by Relational MR Database (RDB) CDSC. T transfer the data to SQL Server using the RDB CDSC’s schema, use RDB CDSC for the transfer: see Transferring data to a relational MR database (RDB).

UNICOM Intelligence Professional automatically converts dates into a format that is supported by SQL Server. The format is YYYY-MM-DD or YY-MM-DD where YYYY and YY is the four-digit or two-digit year number (depending on the number of digits stored in the data), MM is the month number, and DD is the day number.

To run this sample, you need access to a SQL Server installation that contains a database called DMSTransfers.

OutputDatasource(MSSQL)
  ConnectionString = "Provider=MSOLEDBSQL.1; _
    Integrated Security=SSPI; _
    Persist Security Info=False; _
    Initial Catalog=DMSTransfers; _
    Data Source=LocalHost"
  TableOutputName = "short_drinks"
End OutputDatasource

These examples are provided as sample DMS files (called MSAccessTransferToFromRDB.dms, MSExcelTransferToFromRDB.dms, and MSSQLTransferToFromRDB.dms) that are installed with the UNICOM Intelligence Developer Documentation Library. For more information, see Sample DMS files that integrate with Microsoft Office.