InputDataSource section
The InputDataSource section defines the input
data source for the data transformation. The InputDataSource section is required, which means that there must always be at least one InputDataSource section in a DMS file. If you add more than one InputDataSource section to a DMS file, the case data for the input data sources will be combined into a single data source. For more information, see
Using a DMS script to merge data.
Syntax
InputDataSource(name [", description"])
ConnectionString = "<connection_string>"
[SelectQuery = "<select_query>"]
[UpdateQuery = "<update_query>"]
[UseInputAsOutput = True | False"]
[JoinKey = "<variable_name>"]
[JoinType = "Full" | "Inner" | "Left"]
[JoinKeySorted = True | False"]
End InputDataSource
Parameters
name and description define a name and description for the InputDataSource and should be of type Text. You use the name you define here in the Metadata section to identify the data source to which the Metadata section applies.
ConnectionString
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 OLE DB provider. For general information, see
Reading 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 read the case data (this must be a read-enabled CDSC), the Metadata Document (
.mdd) file or other metadata source and MDSC to be used, etc. 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.
When you are using a non-UNICOM Intelligence Data Model OLE DB Provider to write the case data, provided you have specified an input metadata source, you can set the MR Init Category Names connection property to 1 so that the category names are exported instead of the numeric values. However, you will get an error if you use this setting when writing data using the UNICOM Intelligence OLE DB Provider. This means that you cannot use this option when you have more than one OutputDataSource section in your DMS file and one or more of them uses the UNICOM Intelligence OLE DB Provider to write the data.
Each DSC that you can use to read data behaves differently and has different requirements. For specific information about reading data using the read-enabled DSCs that come with the UNICOM Intelligence Data Model, see:
When using the UNICOM Intelligence Data Model, if the metadata associated with the input data source is in a Metadata Document (.mdd) file, specify the name and location of the .mdd file in the Initial Catalog connection property. The .mdd file itself must be writable. For example:
InputDataSource(myInputDataSource, "My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\UNICOM\Intelligence\<version>\DDL\Data\Data Collection File\museum.ddf; _
Initial Catalog=C:\Program Files\UNICOM\Intelligence\<version>\DDL\Data\Data Collection File\museum.mdd"
End InputDataSource
If the
.mdd file has more than one version, the most recent version will be used by default. However, you can select an earlier version by using the
MR Init MDM Version connection property. For more information, see
Selecting a specific version. You can also select multiple versions. This is useful when you want to export data collected using more than one version of the questionnaire. For more information, see
Selecting multiple versions.
If the metadata is not in the form of a Metadata Document (.mdd) file, you specify the metadata source and the MDSC to be used in the Initial Catalog and MR Init MDSC connection properties. The MDSC must be read-enabled. This example specifies the Quanvert Museum sample database and the Quanvert DSC:
InputDataSource("My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrQvDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\
Data\Quanvert\Museum\qvinfo; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Quanvert\Museum\qvinfo; _
MR Init MDSC=mrQvDsc"
End InputDataSource
For some types of data you do not need to specify a metadata source, although generally you will want to do so, because without an input metadata source, you cannot use these event sections:
Moreover, if you are transferring data to a target data source that already exists, the transfer will succeed only if the structure of the output data exactly matches the existing target data. It's possible to transfer data without specifying a metadata source only when using Relational MR Database CDSC, UNICOM Intelligence Data File CDSC, SPSS Statistics SAV DSC, or XML CDSC to write the data (although transferring to a .sav file without a metadata source has some limitations: for more information, see
Transferring data to IBM SPSS Statistics). See below for an example.
If you are using the UNICOM Intelligence Data Model and want to operate on the metadata only, specify the case data in the normal way in the InputDataSource section. Then in the
OutputDataSource section, set the Data Source connection property to CDSC. This specifies the Null DSC, which means that no case data will be written. Do not specify the Null DSC in the InputDataSource section.
SelectQuery
An SQL query that defines a case data filter, a metadata filter, or both a case data and a metadata filter. For more information, see
Filtering data in a DMS file.
If you do not specify a query, it defaults to the following query:
SELECT * FROM vdata
If you are using the UNICOM Intelligence OLE DB Provider (which is part of the UNICOM Intelligence Data Model), this query means that all of the data that can be flattened will be included. However, this query will give you an error if you are using another OLE DB provider. So you must always specify a query when you are using a non-UNICOM Intelligence OLE DB Provider.
Case data will be transferred for the specified variables only. However, if you select a helper variable or a variable that is part of a metadata block (Class object), the parent object will be included in the output metadata. If you specify in the select query 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, but case data will be transferred only for the variable instances specified in the select query.
When you are using the UNICOM Intelligence OLE DB Provider and do not specify a query or you use a SELECT * FROM vdata query, any variables defined in the Metadata section are included in the transformation automatically. However, if you specify the variables individually in the query, you also need to specify in the select query any variables that are defined in the Metadata section. Otherwise the variables defined in the Metadata section will be excluded from the output metadata and the transformation.
The query can be any SQL query that is supported by the OLE DB provider you are using. For information about the SQL queries that are supported, see Basic SQL queries.
UpdateQuery
An SQL statement to be executed on the data source before any processing takes place. 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 read the case data. For more information, see
Supported features of the CDSCs.
Note Use this feature with care because the update query permanently alters the input data source. Take a backup of the input data source before running a DMS file that includes this feature.
UseInputAsOutput, JoinKey, JoinType, and JoinKeySorted
These parameters are used only when running a case data merge. For more information, see
Using a DMS script to merge data.
Examples
1: Using a VDATA select query to filter case data and metadata
The following example specifies the Museum sample UNICOM Intelligence Data File, which consists of case data in the museum.ddf file and a Metadata Document (.mdd) file called museum.mdd.
The query specifies a metadata filter consisting of three named variables (age, gender, museums) and a case data filter that selects female respondents only.
InputDataSource(myInputDataSource, "My 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"
SelectQuery = "SELECT age, gender, museums FROM vdata _
WHERE gender = {female}"
End InputDataSource
2: Using an HDATA select query to filter metadata in the same level
The following query specifies a metadata filter consisting of three named variables (age, gender, name) at the person level.
InputDataSource(myInputDataSource, "My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Data Collection File\Household.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Data Collection File\Household.mdd"
SelectQuery = "SELECT age, gender, name FROM hdata.person"
End InputDataSource
3: Using an HDATA select query to filter metadata in different levels
The following query specifies a metadata filter consisting of three named variables (address, age, country) at different levels via the Up-lev and Down-lev operators (based on the person level).
InputDataSource(myInputDataSource, "My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Data Collection File\Household.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Data Collection File\Household.mdd"
SelectQuery = "SELECT ^.address, age, trip.(country)
FROM hdata.person"
End InputDataSource
Note ^.address is automatically renamed to <FileName>_address (in this example, it is renamed to Household_address).
4: Using a select query to rename metadata
The following query specifies a metadata filter consisting of one renamed variable (age) at the person level.
InputDataSource(myInputDataSource, "My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\
Data Collection File\Household.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Data\Data Collection File\Household.mdd"
SelectQuery = "SELECT age as “person_age” FROM hdata.person"
End InputDataSource
5: Using an update query to delete test data
The following example includes an update query that is used to delete test data from the input data source. Take care when using an update query in the InputDataSource section, because it changes the input data source permanently.
InputDataSource(myInputDataSource, "My 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"
UpdateQuery = "DELETE FROM vdata _
WHERE DataCollection.Status.ContainsAny({Test})"
End InputDataSource
6: Operating on metadata only
The following example shows how to operate on the metadata only. In the InputDataSource section you specify the case data in the normal way and in the OutputDataSource section, the Data Source connection property is set to CDSC, which is the Null DSC and which enables you to operate on the metadata without case data.
InputDataSource(Input)
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"
End InputDataSource
OutputDataSource(Output)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=CDSC"
MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\
Output\MetadataOnly.mdd"
End OutputDataSource
Metadata (ENU, Question, Label, Input)
Entering "Respondents interviewed entering the museum" boolean
expression ("interview = {entering}");
Leaving "Respondents interviewed leaving the museum" boolean
expression ("interview = {leaving}");
End Metadata
Note This example is provided as a sample DMS file (called
MetadataOnly.dms): for more information, see
Sample DMS files.
7: Operating on case data only
The following example shows how to operate on the case data without specifying a metadata source. This is possible only when using Relational MR Database CDSC and IBM SPSS Statistics SAV DSC to write the case data. However, transferring to a
.sav file without using a metadata source has some limitations. For more information, see
Transferring data to IBM SPSS Statistics.
This example transfers case data from a .sav file to another .sav file without using a metadata source:
InputDatasource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrSavDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Sav\Employee data.sav"
SelectQuery = "SELECT id, bdate, educ, salary, salbegin, jobtime, prevexp FROM VDATA"
End InputDatasource
OutputDataSource(Output)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrSavDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\CaseDataOnly.sav"
End OutputDataSource
Note This example is provided as a sample DMS file (called
CaseDataOnly.dms): for more information, see
Sample DMS files.
8: Using other OLE DB providers
The following example uses the Microsoft OLE DB Provider for ODBC Drivers to read data in an Access database:
InputDataSource(Input)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrADODsc; _
Location=""Provider=MSDASQL.1; _
Persist Security Info=False; _
Extended Properties='DSN=MS Access Database; _
DBQ=C:\Inetpub\iissamples\sdk\asp\database\
Authors.mdb; _
DriverId=281; _
FIL=MS Access; _
MaxBufferSize=2048; _
PageTimeout=5; _
UID=admin'""; _
MR Init Project=Authors"
End InputDatasource
The example uses the UNICOM Intelligence ADO DSC in the UNICOM Intelligence Data Model to read the Access database as an ADO data source. Although not demonstrated here, it is also possible to specify an input metadata source when using the ADO DSC, which allows you to create an output metadata document (
.mdd) file. For more information, see
Transferring data from Microsoft Office.
Note To run this sample, you must have the Microsoft OLE DB Provider for ODBC Drivers, the ODBC data source called MS Access Database, and the Authors sample Access database, which all come with Microsoft Office. In the UNICOM Intelligence Developer Documentation Library, there are similar example scripts called MSAccessToQuantum.dms and MSAccessToSav.dms.
See also