OutputDataSource section

The OutputDataSource section defines an output data source for the data transformation. There must be at least one OutputDataSource section in a DMS file and you can optionally specify more than one. If you specify more than one OutputDataSource section in a DMS file, the case data will be written to each output data source specified. This is useful, if, for example, you want to export the case data to two or more different formats or locations at the same time.

OutputDataSource(
name [, "description"])
   [ConnectionString = "<connection_string> |
      UseInputAsOutput = True|False"]
   [MetaDataOutputName = "<metadata_location>"]
   [UpdateQuery = "<update_query>"]
   [TableOutputName = "<table_output_name>"]
   [VariableOrder = "SELECTORDER"]
End OutputDataSource

This specifies the OLE DB connection properties, which define the OLE DB provider to be used and all of the details about the physical data source, such as its name and location. Each OLE DB provider has different requirements for the connection string. For specific information about non-Data Model OLE DB providers, see the documentation that comes with the provider. For general information, see Writing data using other OLE DB providers.

If you want to use the UNICOM Intelligence Data Model, specify the UNICOM Intelligence OLE DB Provider by setting the Provider connection property to mrOleDB.Provider.n (where n is the version number). The UNICOM Intelligence OLE DB Provider has some custom connection properties that define the CDSC that is to be used to write the case data (this must be a write-enabled CDSC), the data validation settings, etc. The Initial Catalog connection property should not be given a value; if specified, it should be set to an empty string (""). For more information, see Connection properties.

You can specify file locations using a relative path (relative to the folder in which the DMS file is located when you run it). Generally, you do not need to specify a connection property for which the default value is being used.

If you specify a physical data source that does not already exist, generally it will be created when the DMS file is executed, provided the specified folder exists. However, when exporting to a relational MR database, you must create the actual database before you run the DMS file. For more information, see Transferring data to a relational MR database (RDB).

▪If you are doing a case-data only transformation or are not using the UNICOM Intelligence Data Model to read the data, the transfer will succeed only if the structure of the output data exactly matches the existing data.

▪If you are using the UNICOM Intelligence Data Model to read the data and have specified an input metadata source, what happens depends on whether the output of the data transformation matches the structure of the data in the output data source, whether the cases already exist in the target data source, and on the format of the data (and hence the CDSC being used).

If the metadata to be written will not be in the form of a Metadata Document (.mdd) file, you can specify the metadata file to be written and the MDSC to be used in the Initial Catalog and MR Init MDSC connection properties. The MDSC must be write-enabled. At present, the only DSC that can be used in this way to write a metadata file other than a .mdd file is the Triple-S DSC (see Transferring data to Triple-S.

Each DSC that you can use to write data behaves differently and has different requirements. For specific information about writing data using some of the write-enabled DSCs that come with the UNICOM Intelligence Data Model, see:

If you are using the UNICOM Intelligence Data Model and want to operate on the metadata only, specify the Data Source connection property as CDSC. This is a special CDSC, called the Null DSC, which enables you to connect to the UNICOM Intelligence OLE DB Provider without any case data.

Note An easy way to create the connection string is to use the UNICOM Intelligence Professional Connection String Builder. For more information, see 3. Transferring different types of data.

This defines the name and location of the Metadata Document (.mdd) file to which the exported metadata is to be written. If you do not specify this parameter, the output metadata is not saved. The output metadata determines the structure of the output case data. For example, if the output case data source does not exist, it will be created based on the output metadata. If the output case data source does exist and the data format is one that can be updated, the output case data is synchronized with the output metadata.

▪If the DMS file contains a Metadata section that relates to the input data source, the metadata defined in the Metadata section is merged with the input data source metadata. The input data source metadata is used as the master version for the merge.

▪The merged metadata is then filtered according to the metadata filter defined in the select query in the InputDataSource section (see InputDataSource section). If the select query includes a helper variable or a variable that is part of a metadata block (Class object), the parent object will also be included in the output metadata. If the select query includes one or more variable instances that relate to a variable inside a grid or a loop, all of the related variable instances will be included in the output.

▪The output metadata will not contain any versions, even if the input metadata source contains versions. By default, the output metadata will be based on the most recent version of the input metadata source. However, if the input metadata source has multiple versions, and you specify one or more specific versions to use for the transfer (using the MR Init MDM Version connection property in the InputDataSource section (see InputDataSource section), the output metadata will be based on the specified version or combination of versions.

When you are transferring data to a .sav file, it is recommended that you save the output metadata file, whenever possible. If you subsequently want to read the .sav file using the Data Model, it is usually preferable to do so using the .mdd file, because this gives you access to the original UNICOM Intelligence Data Model variable names. In addition, if you subsequently want to export additional records to the .sav file, it will be possible only if you run the export using this file as the input metadata source. For more information, see Transferring data to IBM SPSS Statistics.

Note Starting with version 5.6, the input metadata will be merged to any existing output metadata (with the existing output acting as the master). A merge is used, instead of simply overwriting the existing metadata, to ensure that the category map for the existing output is used. For example, if data is collected using two separate clusters, the category maps may be different on each cluster. Similar to a vertical merge, when appending data to a combined data set, the category map for the output dataset needs to be used. Prior to version 5.6, the output metadata was overwritten, invalidating any existing data.

Case data-only transformation. No output metadata is created if you do not specify a metadata source in the InputDataSource section (for example, because you are using a non-UNICOM Intelligence Data Model OLE DB provider to read the data or you are doing a case data-only transformation), and the output case data structure is based on the attributes and names in the input case data. If you specify the MetaDataOutputName parameter in this situation, it will be silently ignored.

An SQL statement to be executed on the data source after the processing of the procedural code defined in all of the Event sections with the exception of the OnAfterJobEnd Event section. For more information, see 4. Using an update query.

Any Data Manipulation SQL syntax that is supported by the OLE DB provider can be used. When you are using the UNICOM Intelligence OLE DB Provider you can use an INSERT, UPDATE, or DELETE statement, provided the syntax is also supported by the CDSC that is being used to write the case data. For more information, see SQL syntax and Supported features of the CDSCs.

Set to True if your DMS file has a single input data source and you want the data to be written to that data source. This means that the input data source will be overwritten with the results of the data transformation. The default is False. If you are using the UNICOM Intelligence Data Model to read the input data, the CDSC must be write-enabled and support changing the data in existing records (Can Update is True for the CDSC).

If you want the data to be written to the input data source when you are running a case data merge, and therefore your DMS file contains multiple input data sources, you must specify the UseInputAsOutput option in the relevant InputDataSource section (see InputDataSource section) and not in the OutputDataSource section.

When you use the UseInputAsOutput option, the version history in the input metadata will be preserved only if, by default, you are using the most recent version. A new unlocked version will be created if the most recent version is locked. However, if you specify one or more versions using the MR Init MDM Version connection property in the InputDataSource section (see InputDataSource section), the input metadata will be overwritten with the specified version or combination of versions and the version history will be deleted.

Note The UseInputAsOutput option should be used with caution because it changes the input metadata and case data irreversibly. It is not suitable for use with data that is in the process of the being collected by a live UNICOM Intelligence Interviewer - Server project. It is recommended that you take a backup of your data before using this option.

Used only when writing the case data using a non-UNICOM Intelligence Data Model OLE DB provider, this specifies the name of the table to which the data is to be written.

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 always fail.

Variables are output in the order in which they appear in the Select Query statement in the InputDataSource section (see InputDataSource section). In UNICOM Intelligence 3.0 – 5.5, variables were output in the order in which they appeared in the input metadata source, unless the output data set specified in OutputDataSource already existed. You can delete the output data set in the event OnBeforeJobStart section, or use property MR Init Overwrite, to ensure that variables are output in the same order as the Select Query statement in the InputDataSource section.

OutputDataSource(myOutputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrSavDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\Simple.sav"
  MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\Simple.mdd"
End OutputDataSource

The following example specifies that the case data is to be written to a UNICOM Intelligence Data File (.ddf) and shows an update query. This uses the Now function to set the DataCollection.FinishTime variable to the current date and time.

OutputDataSource(myOutputDataSource, "My output data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\UNICOM\Intelligence\<version>\DDL\Output\UpdateQuery.ddf"
  MetaDataOutputName = "C:\Program Files\UNICOM\Intelligence\<version>\DDL\Output\UpdateQuery.mdd"
  UpdateQuery = "UPDATE vdata _
    SET DataCollection.FinishTime = Now()"
End OutputDataSource

InputDataSource(Input, "The input data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\UNICOM\Intelligence\<version>\DDL\Output\museum-copy.ddf; _
    Initial Catalog=C:\Program Files\UNICOM\Intelligence\<version>\DDL\Output\museum-copy.mdd; _
    MR Init MDM Access=1"
  SelectQuery = "SELECT DataCollection.FinishTime FROM vdata"
End InputDataSource

OutputDataSource(Output, "The output data source")
  UseInputAsOutput = "True"
End OutputDataSource

For an example of using the Null DSC and operating on metadata only, see the third example in the InputDataSource section (see InputDataSource section). For an example of operating on case data only, see the fourth example in the same topic.

This example shows exporting a subset of the Museum sample data set to Excel using a non-UNICOM Intelligence Data Model OLE DB provider. The MR Init Category Names connection property has been set to 1 in the InputDataSource section so that the category names are transferred to Excel rather than the category values. This generally makes the data easier to interpret. For more information, see Writing data using other OLE DB providers.

Note This example is provided as a sample DMS file, called MSExcelTransferToFromDDF.dms, that is installed with the UNICOM Intelligence Developer Documentation Library. For more information, see Sample DMS files that integrate with Microsoft Office.

InputDataSource(Input, "The input data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd; _
  MR Init Category Names=1"
  SelectQuery = "SELECT interest, age, gender, expect,
                 when_decid _
                 FROM VDATA WHERE gender = {female}"
End InputDataSource

OutputDatasource(MSExcel)
  ConnectionString = "Provider=MSDASQL.1; _
    Persist Security Info=False; _
    Data Source=Excel Files; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\MSExcelTransferToFromDDF.xls"
  TableOutputName = "Sheet1"
End OutputDatasource