Data Import configuration

The configuration of the Data Import service is managed using the following configuration files.

Database.config

This file is located in the Config folder where the Data Import is installed and contains a connection string to the Q_Panel Master database which is set on installation. For clients using Q_Community, a connection string to the Kentico database must also be added.

<connectionStrings>
  	<add name="MasterDatabase" connectionString="Data Source=SERVERNAME\SQL2008;Initial Catalog=MARSCMaster;Integrated Security=False;User ID=marscuser;Password=xyz" providerName="System.Data.SqlClient"/>
  	<add name="CMSConnectionString" connectionString="Persist Security Info=False;database=KenticoMarsc.Community;server=SERVERNAME\SQL2008;user id=marscuser;password=xyz;Current Language=English;Connection Timeout=240;"/>
</connectionStrings>

Marsc.Service.DataImport.Config.xml

This file is also located in the Config folder where the Data Import is installed. It contains the location of the log file, the Q_Panel child database to be used, and the locations of the specific configuration files for each of the types of import defined.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <log>
    <DefaultTextLog file="C:\Program Files\MARSC\Data Import\Log\Service.DataImport.Log" logNamedThreads="true" maxLevel="10" />
    <DefaultEventLog source="Marsc.Service.DataImport" maxLevel="1" />
  </log>
  <dataImport>
    <childDatabaseName>ChildDB</childDatabaseName>
    <panellistSet>
       <panellist type="triples" configSource="Config\TripleS.Panellist.Config.xml" />
     <panellist type="MIPro" configSource="Config\MiPro.Panellist.Config.xml" />
    </panellistSet>
    <backfeedSet>
        <backfeed type="MarscXml" configSource="Config\MarscXml.Backfeed.Config.xml" />
        <backfeed type="MarscCsv" configSource="Config\MarscCsv.Backfeed.Config.xml" />
    </backfeedSet>
  </dataImport>
</configuration>

This example shows all four possible configuration types, standard Triple-S, Q_Survey (MiPro) and two different Backfeed configuration files, one for backfeed specified as .csv and one for xml.

Data Import log files

The panellistSet and backfeedSet are processed at the same time in separate threads. This means that panellist and backfeed records may be imported at the same time. Log entries for both actions will appear intertwined in the log file. To help manage this, logNamedThreads="true" can be added to the log configuration (see example above). This will cause all the different named threads to produce their own log files. As a result you should get the following log files:

Service.DataImportyyyymmdd.log
Service.DataImportyyyymmdd-Backfeed Import(MarscCsv).log
Service.DataImportyyyymmdd-Backfeed Import(MarscXml).log
Service.DataImportyyyymmdd-Panellist Import.log
Service.DataImportyyyymmdd-Scheduler.log

Triple-S Configuration file

The Triple-S configuration file (for standard Triple-S with data files) defines the types of panellist import required, the location and naming of drop files, and any special fields required. Unless a change to defaults as installed is required, configuration does not need to be set and the installed config file can be left as is.

The ordering and possible values set for each of the tags is important, the example below indicates the correct ordering.

Below is an example of the possible configurations:

<?xml version="1.0" encoding="utf-8"?>
<panellist>
  <schedule>
  </schedule>
  <ageColumnSet />
  <importSet>
    <import groupName="title" importName="Variable Import" labelName="label">
      <ignoreColumnSet />
      <specialColumnSet />
      <variable/>
    </import>
    <import groupName="title" importName="Single Import">
      <ignoreColumnSet />
      <specialColumnSet />
      <single/> 
    </import>
    <import groupName="title" importName="Paired Import" labelName="label">
      <ignoreColumnSet />
      <specialColumnSet />
      <paired dataExtension="csv" fileRegex="^.*" schemaExtension="sss" sortGroup="1" />
    </import>
    <import groupName="title" importName="Sequence Import" labelName="label">
      <ignoreColumnSet />
      <specialColumnSet />
      <sequence dataExtension="csv" fileRegex="^Sequence(\d+).*" schemaExtension="sss" sequenceGroup="1" />
    </import>
  </importSet>
</panellist>

The example above shows that all four import types can be defined within the same Triple-S configuration file (if required), and should all be contained within the <importSet> tag. This example also shows the placement of the various settings and attributes that can be used in the definition – these are explained in more detail in the later sections of this article.

Drop folders can be located on an FTP server as long as the FTP server is configured to run on the same server as the Data Import service. Drop folders should not be located under Program Files.

Variable Import

A variable type import is used to populate the Q_Panel child database with the required variables, but will not import any data.
As a minimum, a variable import can be defined as follows:

<import groupName="title" importName="Variable Import" labelName="label">
     <variable/>
</import>

This will use the default values for the schema file name and path and is the equivalent of specifying the following.

<import groupName="title" importName="Variable Import" labelName="label">
  <variable schemaFileName="Variables.sss">
    <schema path=".\Drop Folder\Schema\Variable">
      <success action="move" path=".\DropFolder\Schema\Variable\Success" />
      <failure action="move" path=".\DropFolder\Schema\Variable\Failure" />
      <unmatched action="delete" />
    </schema/>
  </variable>
</import>

The schema file name and path location can be changed as required.

Note that the specialColumn “id” which defines the external customer reference must also be included (as a minimum) within each import definition.

Single Import

A single import type uses a single Triple-S schema file and one or more data files.

As a minimum, a single import can be defined as follows:

<import groupName="title" importName="Single Import" labelName="label">
     <single/>
</import>

This will use the default schema and data file names and paths. This is the equivalent of specifying the following.

<import groupName="title" importName="Single Import" labelName="label">
  <single dataFileRegex="^*.\.csv$" schemaFileName="Profiler.sss" sortGroup="1">
    <data path="\Drop Folder\Data\Single">
      <success action="move" path=".\DropFolder\Data\Single\Success" />
      <failure action="move" path=".\DropFolder\Data\Single\Failure" />
      <unmatched action="delete" />
    </data>
    <schema path=".\Drop Folder\Schema\Single">
      <success action="move" path=".\DropFolder\Schema\Single\Success" />
      <failure action="move" path=".\DropFolder\Schema\Single\Failure" />
      <unmatched action="delete" />
    </schema>
  </single>
</import>

The file names and paths can be changed as required.

When running single imports the Triple-S definition file (.sss) will stay in the drop folder (unless it fails to load or is unmatched) and can be used to process several data files as and when they are dropped in to the relevant data path location.

Note that the specialColumn “id” which defines the external customer reference must also be included (as a minimum) within each import definition.

Paired Import

A paired import type requires the schema and data files to have matching names which differ only by the file extension. A paired import is only processed when the schema and data files are both available for processing.

As a minimum, a paired import can be defined as follows:

<import groupName="title" importName="Paired Import" labelName="label">
     <paired/>
</import>

This will use the default schema and data file extensions, name and paths. This is the equivalent of specifying the following.

<import groupName="title" importName="Paired Import" labelName="label">
  <paired dataExtension="csv" fileRegex="^.*" schemaExtension="sss" sortGroup="1">
    <data path="\Drop Folder\Data\Paired">
      <success action="move" path=".\DropFolder\Data\Paired\Success" />
      <failure action="move" path=".\DropFolder\Data\Paired\Failure" />
      <unmatched action="delete" />
    </data>
    <schema path=".\Drop Folder\Schema\Paired">
      <success action="move" path=".\DropFolder\Schema\Paired\Success" />
      <failure action="move" path=".\DropFolder\Schema\Paired\Failure" />
      <unmatched action="delete" />
    </schema>
  </paired>
</import>

The file name, extension and paths can be changed as required.

Note that the specialColumn “id” which defines the external customer reference must also be included (as a minimum) within each import definition.

Sequence Import

A sequence import type is similar to a paired import type, but with the additional restriction that the schema and data files must be processed in sequence order. This means that the filenames must contain a sequence number and the fileRegex parameter must contain an expression to extract the sequence number.

As a minimum, a sequence import can be defined as follows:

<import groupName="title" importName="Sequence Import" labelName="label">
     <sequence/>
</import>

This will use the default schema and data file extensions, name and paths. This is the equivalent of specifying the following.

<import groupName="title" importName="Sequence Import" labelName="label">
  <sequence dataExtension="csv" fileRegex="^.*" schemaExtension="csv" sequenceGroup="1">
    <data path="\Drop Folder\Data\Paired">
      <success action="move" path=".\DropFolder\Data\Sequence\Success" />
      <failure action="move" path=".\DropFolder\Data\Sequence\Failure" />
      <unmatched action="delete" />
    </data>
    <schema path=".\Drop Folder\Schema\Paired">
      <success action="move" path=".\DropFolder\Schema\Sequence\Success" />
      <failure action="move" path=".\DropFolder\Schema\Sequence\Failure" />
      <unmatched action="delete" />
    </schema>
  </sequence>
</import>

The file name, extension and paths can be changed as required.

Note that the specialColumn “id” which defines the external customer reference must also be included (as a minimum) within each import definition.

Other Triple-S Import Settings & Attributes

The following Triple-S settings apply to standard Triple-S using data files and to the Q_Survey plugin mode.

This setting defines which fields from the Triple-S file are to be used to generate an age field. The regex parameter defines a regular expression which identifies the age field. This setting will apply to all data imports defined in the configuration file.

An age variable will be created in the Q_Panel database which will be kept in sync with the date variable it is associated with.

<ageColumnSet>
    <ageColumn regex="^DOB.*"/>
    <ageColumn regex="^DateOfBirth.*"/>
</ageColumnSet>

The groupName attribute specifies the element which names the import and can be set to “name” or “title”. In Triple-S the survey element can have a name or title or both:

<survey>
    <name>My survey name</name>
    <title>My very interesting survey title</title>
    ...
</survey>

For the above survey, specifying groupName="title" would create an import named "My very interesting survey title". If you used groupName="name" the import would be "My survey name". As both these tags are optional in Triple-S, if neither of these are supplied the name of the Triple-S file is used, concatenated with the date and time in UTC.

For example, if the specified groupName is not supplied and the import was started at 19:31:44 on 21st July 2014 using a Triple-S file called ‘Profiler 1’, then the import would be titled "Profiler 1 (2014-07-21 19:31:44Z)".

This name will be seen as a folder in Hierarchy Manager when new variables are imported, and in Import Manager for each import processed.

As these fields are missing from Q_Survey generated Triple-S (unless manually added), the name, as displayed in Import Manager and Hierarchy Manager will use the Triple-S file name and UTC date-time.

The importName must be unique per import. It has special significance for sequence type processing. Sequence numbers are associated with "importName" and numbering starts at 1. Once a sequence import has been successfully processed the sequence number is incremented by 1. To re-start sequence numbering back at 1, either the table must be edited (MARSC_ImportSequence) or the "importName" used in the configuration changed.

The attribute labelName determines which element of the Triple-S variable definition is to be used as the variable name in Q_Panel. It can either be set to name or label. The default value is name.

This setting is specific to an import. It allows columns from the Triple-S schema file that match the regular expression to be ignored. It supports regex, or you can name a column specifically:

<ignoreColumnSet>
    <ignoreColumn regex="Address Line 2"/>
    <ignoreColumn regex="Address Line 3"/>
    <ignoreColumn regex="^xxx.*$"/>
</ignoreColumnSet>

Note that if both <ignoreColumnSet> and <specialColumnSet> are defined for an import, they must be specified in that order to avoid a failure.

This setting is specific to an import. It defines which columns from the Triple-S schema file are the Q_Panel columns with special significance, such as id and email address. It is very important that these fields are set up correctly. For example:

<specialColumnSet>
    <specialColumn name="id" value="PanellistID"/>
    <specialColumn name="firstName" value="FirstName"/>
    <specialColumn name="lastName" value="LastName"/>
    <specialColumn name="email" value="EmailAddress"/> 
    <specialColumn name="isEnabled" value="Member Enabled" />
</specialColumnSet>

The name tag identifies the special column and the value tag identifies the variable in the Triple-S definition. (Note that the tags are case-sensitive).
As a minimum, the id column should be defined, as all data files must include a key to uniquely identify a panellist, typically an id field or email address. PanellistID is the default so only needs changing if the key panellist ID is not named “PanellistID”.

Columns that are not identified in this section will be assumed to be profile data with no special significance.

The full list of tags for the special columns (which are Q_Panel placeholders) are:

Id - this can be a numeric or character variable
firstName
lastName
email
CintPanelId
loginName
password - only plain text passwords are supported
isEnabled - this must be a logical variable. It can have values of True/False, Yes/No or 1/0.

The behaviour of the special fields loginName, password and isEnabled depends on the whether the Q_Panel installation also uses Q_Community.

	loginName and password
For installations NOT using Q_Community	If the data file specifies login names and passwords, only the login name will be stored in Q_Panel. (Q_Panel does not have any use for the password).
For installations using Q_Community	If the data file specifies login names and passwords, these will not be stored in Q_Panel, only in Kentico. (Note that Data Import only supports plain text passwords which will be encrypted when they are stored in Kentico).

isEnabled

For installations NOT using Q_Community

If the data file specifies the isEnabled column, panellists will be enabled or disabled depending on the value of this column.

If the data file does not specify isEnabled, then all new panellists will automatically be enabled.

For installations using Q_Community

If the data file specifies the isEnabled column, then the behaviour is more complex as kentico rules are also enforced:

Inserting new panellists:
• If the data file specifies the “isEnabled” column, the panellist will only be enabled if the value of “isEnabled” is set to True, and the password AND login name are supplied.
• If the data file does not specify the “isEnabled” column the panellist will only be enabled if the password AND login name are supplied.

Updating existing panellists:
• If the data file has "isEnabled" set to False, the panellist will be disabled
• If the data file has "isEnabled" set to True, the panellist will only be enabled if the panellist has a password in Kentico
• If the data file does not specify the "isEnabled" column the panellist status will not change

regex

Note that regex uses C# regular expression syntax.

Q_Survey Configuration File

The Q_Survey configuration file defines the location of the schema drop folder and the Q_Survey API token. It can also specify any special fields required (i.e. those listed above), scheduling requirements and the fail threshold.

For example, the default configuration file is as follows:

<?xml version="1.0" encoding="utf-8"?>
<panellist>
  <importSet>
    <import groupName="title" importName="MI Pro API Import" labelName="label">
      <ignoreColumnSet>
        <ignoreColumn regex="^AltIdNumeric$" />
      </ignoreColumnSet>
      <specialColumnSet>
        <specialColumn name="id" value="AAltidM!.1" />
      </specialColumnSet>
      <api token="1abc2345-6d7e-890f-1234-abcd1234efgh" />
    </import>
  </importSet>
</panellist>

This uses the default schema path and is the equivalent of specifying the following.

<api token="1abc2345-6d7e-890f-1234-abcd1234efgh ">
    <schema path=".\Drop Folder\Schema\Api"/>
</api>

The paths can be changed as required.

Drop folders can be located on an FTP server as long as the FTP server is configured to run on the same server as the Data Import service. Drop folders should not be located under Program Files.

Note that:

the specialColumn “id” which defines the external customer reference must be defined as in the example above.
when configuring the questionnaire URL (in the email survey invite) you must include the parameter value altid which will be set to the external panellist ID (in the Q_Panel database). For example, the questionnaire URL might look like this in a survey mailing:

http:// dc.miprocloud.net/DCWebEngine/panelsurvey.aspx?qif=ab1c2de3-fbcf-4567-a4f5-fb1234c9b72f&altid= [ExternalID]&rid=[EmailResponseId]

If this is not setup correctly Data Import will fail to process the record and will stop processing any further responses for the questionnaire. In these circumstances action must be taken by QuenchTec support.

Marsc Csv Backfeed Configuration File

The csv backfeed configuration file defines the location and naming of .csv backfeed files and the fail threshold.

For example:

<?xml version="1.0" encoding="utf-8"?>
<backfeed>
  <schedule>
    <continuous/>
  </schedule>
  <importSet>
    <import importName="Backfeed">
      <failThreshold>2</failThreshold>
      <marscCsv dataFileRegex="^*.csv$" sortGroup="1">
        <data path="C:\MARSC\Drop Folder\Data\marscCSV"/>
      </marscCsv>
    </import>
  </importSet>
</backfeed>

The backfeed file name and path location can be changed as required.

Drop folders can be located on an FTP server as long as the FTP server is configured to run on the same server as the Data Import service. Drop folders should not be located under Program Files.

The fail threshold determines where the file is placed in the event of errors in the backfeed records. All valid backfeed records will be inserted, but if the number of backfeed records that are rejected reaches the fail threshold the file will be moved to the Failure sub-folder after processing. The log file shows the number of backfeed records that were accepted and the number that were rejected.

The fail threshold is optional and the default value is 0 which means that even if all records are rejected the file will still be moved to Success. The backfeed file will be always be moved to Failure if the error is more severe, for example, if the file is not in the correct format.

As a minimum, a csv backfeed can be defined as follows:

<import importName="Backfeed">
    <marscCsv/>
</import>

This will use the default values for the backfeed file name and path and is the equivalent of specifying the following.

<import importName="Backfeed">
    <failThreshold>0</failThreshold>
        <marscCsv dataFileRegex="^*.csv$" sortGroup="1">
            <data path=".\Drop Folder\Data\marscCsv"/>
    </marscCsv>
</import>

CSV backfeed file format

Each row in the csv file should consists of between three and nine fields which map into Q_Panel as follows:

Position	MARSC field name	Date (time) format
1	UniqueRef
2	Email Resp ID*
3	Outcome Code
4	Interview Date	yyyy-mm-dd
5	Don’t Contact Before Date	yyyy-mm-dd
6	Re-contact Date	yyyy-mm-dd hh:mm:ss
7	Reward Points
8	Start Interview Time	yyyy-mm-dd hh:mm:ss
9	End Interview Time	yyyy-mm-dd hh:mm:ss

Either positions 1 or 2 must contain data which identify the panellist.

Position 3 must contain the outcome code.

Positions 4-9 can be set as optional or required according to the definition of the outcome code.

For example, a csv backfeed file could contain these rows:

159805,,1
159799,,1,,,,
159875,,4,2015-01-18,,,2
,19f290c75bbd4c729b6abb21a9337b67,4,2012-5-17,,,2
159885,,1,2015-01-20,,,10,2015-01-20 09:10:11,2015-01-20 09:15:11

Note that date time fields may also use the format yyy-mm-ddThh:mm:ss.

Marsc XML Backfeed Configuration File

The XML backfeed configuration file defines the location and naming of xml backfeed files and the fail threshold.

For example:

<?xml version="1.0" encoding="utf-8"?>
<backfeed>
    <schedule>
        <continuous/>
    </schedule>
    <importSet>
        <import importName="Backfeed">
            <failThreshold>2</failThreshold>
            <marscXml dataFileRegex="^*.xml$" sortGroup="1">
                <data path="C:\MARSC\Drop Folder\Data\marscXML"/>
            </marscXml>
        </import>
    </importSet>
</backfeed>

The backfeed file name and path location can be changed as required.

Drop folders can be located on an FTP server as long as the FTP server is configured to run on the same server as the Data Import service. Drop folders should not be located under Program Files.

As a minimum, an xml backfeed can be defined as follows:

<import importName="Backfeed">
    <marscXml/>
</import>

This will use the default values for the backfeed file name and path and is the equivalent of specifying the following.

<import importName="Backfeed">
    <failThreshold>0</failThreshold>
    <marscXml dataFileRegex="^*.bkf$" sortGroup="1">
      <data path=".\Drop Folder\Data\marscXml"/>
    </marscXml>
</import>

Note that by default the XML backfeed file is expected to have the file extension .bkf.

XML backfeed file format

Each row in the xml file should consists of between three and nine tags which map into Q_Panel as follows:

Tag name	Date (time) format
<UniqueRefNo>
<ResponseId>
<OutcomeCode>
<DateofContact>	yyyy-mm-dd
<DontContactBeforeDate>	yyyy-mm-dd
<ReContactDateTime>	yyyy-mm-ddThh:mm:ss
<RewardPoints>
<InterviewStartDateTime>	yyyy-mm-ddThh:mm:ss
<InterviewEndDateTime>	yyyy-mm-ddThh:mm:ss

Either <UniqueRefNo> or <ResponseId> must be included to identify the panellist and the <OutcomeCode> also must be included.

The other fields can be included if required according to the definition of the outcome code.

For example, an xml backfeed file could contain the following records:

<ProjectBackfeed>
   <Backfeed>
      <UniqueRefNo>4891024</UniqueRefNo>
      <OutcomeCode>4</OutcomeCode>
      <DateOfContact>2015-02-25</DateOfContact>
      <DontContactBeforeDate>2015-03-25</DontContactBeforeDate>
      <ReContactDateTime>2015-04-01T09:30:47</ReContactDateTime>
      <RewardPoints>5</RewardPoints>
      <InterviewStartDateTime>2015-02-25T09:30:47</InterviewStartDateTime>
      <InterviewEndDateTime>2001-12-25T09:37:21</InterviewEndDateTime>
   </Backfeed>
   <Backfeed>
      <ResponseId>808efddc5ba84793997ab835413c0f33</ResponseId>
      <OutcomeCode>9</OutcomeCode>
   </Backfeed>
</ProjectBackfeed>

Scheduling

The schedule defines the frequency of processing.

The schedule can be set to continuous, hourly, daily, weekly or monthly. For each of these options there is a default setting, but they can each be specified as required if the default is not suitable.

As a minimum, a continuous schedule can be defined as follows:

<schedule>
    <continuous/>
</schedule>

If set to <continuous/> the Data Import service will run a continuous schedule, polling for new files every 60 seconds.

The XML showing all options is shown below:

<continuous pollingInterval="60" startTime="00:00" endTime="00:00">
   <exclude>
        <sunday/>
        <monday/>
        <tuesday/>
        <wednesday/>
        <thursday/>
        <friday/>
        <saturday/>
    </exclude>
</continuous>

The polling interval is in seconds and specifies how often the scheduler will run.

The start time and end time are periods during the day (24 hour clock) when the schedule will process data import schemas.

Any days appearing in the exclude tag will be completely excluded.

For example, to run from Monday to Friday 09:00 to 17:30 running every 5 minutes, the XML would be:

<continuous startTime="09:00" endTime="17:30" pollingInterval="300">
    <exclude>
        <sunday/>
        <saturday/>
    </exclude>
</continuous>

As a minimum, an hourly schedule can be defined as follows:

<schedule>
    <hourly/>
</schedule>

If set to <hourly/> the Data Import service will run the default hourly schedule, polling for new files every hour.

The XML showing all the options is shown below:

<hourly minutes="00" startTime="00:00" endTime="00:00">
    <exclude>
        <sunday/>
        <monday/>
        <tuesday/>
        <wednesday/>
        <thursday/>
        <friday/>
        <saturday/>
    </exclude>
</hourly>

The minutes specifies the minutes past each hour when the scheduler runs.

The start time and end time are periods during the day (24 hour clock) when the schedule will process data import schemas.

Any days appearing in the exclude tag will be completely excluded.

<daily>

As a minimum, a daily schedule can be defined as follows:

<schedule>
    <daily/>
</schedule>

If set to <daily/> the Data Import service will run the default daily schedule, polling for new files every day at midnight (00:00).
The XML showing all the options is shown below:

<daily time="00:00">
    <exclude>
        <sunday/>
        <monday/>
        <tuesday/>
        <wednesday/>
        <thursday/>
        <friday/>
        <saturday/>
    </exclude>
</daily>

The time specifies the time of day when the scheduler runs (24 hours clock).

Any days appearing in the exclude tag will be completely excluded.

As a minimum, a weekly schedule can be defined as follows:

<schedule>
    <weekly/>
</schedule>

If set to <weekly/> the Data Import service will run the default weekly schedule, polling for new files every week on Monday at midnight (00:00).

The XML showing all the options is shown below:

<weekly dayOfWeek="Monday" time="00:00"/>
</weekly>

The dayOfWeek sets the days the scheduler runs, valid values are Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.

The time specifies the time of day when the scheduler runs (24 hour clock).

As a minimum, a monthly schedule can be defined as follows:

<schedule>
    <monthly/>
</schedule>

If set to <monthly/> the Data Import service will run the default monthly schedule, polling for new files on the first of every month at 00.00.

The XML showing all the options is shown below:

<monthly dayOfMonth="1" time="00:00"/>

The dayOfMonth sets the day the scheduler runs – valid values are 1-31.

The time specifies the time of day when the scheduler runs (24 hour clock).