Generic Data Import Export

Files Preload Mechanism

Both "catalog" and "single file" databases use files preload mechanism. The only difference is that in first case you can specify several files and only one file in second case. Both use the same file selection dialog:

As you see, you can specify three types of location:

1. Upload - file is uploaded from client computer and saved in the temporary folder. In this case "Login" and "Password" fields are not available. You can drag and drop the file onto the DROP HERE placeholder, or click the placeholder and use the dialog that appears to locate and upload the file. As you understand, you should manually upload the file on each execution, there's no sense to schedule such import sequence. Standard html control <input type="file"> is used for upload. The maximum allowable file size is defined by the "maxRequestLength" attribute of the <httpRuntime> element of the "machine.config" file. Default value is about 4MB.
    
2. LAN - file is copied from network or local location to the temporary folder. The file location should be directly accessible from the server. You must specify network path (e.g. "\\Puavno01\Share\Test3.csv") or local path, if Web server is running on your computer (e.g. "C:\Dev\Test\Test3.csv") in the File Path field. 
   
   Remember that ASP.NET process usually runs under account with limited rights, so usually you should specify some credentials to access the location (the "Login" and "Password" fields, "password" value is stored in encrypted state). On sequence execution, these credentials will also be used by the hosting windows service (see "Components interaction" section of this article). 
   Internally, impersonation and "System.IO.File.Copy" is used. Also, remember that if you specify incorrect password, specified login could be blocked after some amount of unsuccessful logons.
    
3. Web - file is downloaded from the specified location and saved in the temporary folder. Currently, "ftp", "http", and "https" protocols are supported. Credentials can also be specified. Internally, "System.Web.WebRequest" is used.
   When you click "Additional Settings" on the "Data source definition" page, all "LAN" files are copied and "Web" files are downloaded. The "Upload" files are processed earlier.
   
   Temporary folder location depends on running process. When you configure import sequence with the wizard, then "C:\Documents and Settings\<CompName>\ASPNET\Local Settings\Temp" is used. When sequence is executed, then "C:\Windows\Temp" is used. All temporary folders will be deleted if running process terminates correctly.

Defining Simple Table 

By default, the system will automatically select first table from your data source. In the simplest scenario, you can simply click "Mapping Rule Settings" to proceed to the next step. If you want to preview your data first, click PREVIEW. 

When we say "simple" table, we mean that it contains one row per destination object. For example, if we are importing users, then one row contains information about one user. This fact is reflected by the "Simple table" radio-button option.

Defining Complex Table

Sometimes you have to import more complex data. Imagine that you have tables "Users" and "Accounts". Table "Accounts" has column "Owner" that contains ID of the appropriate user (foreign key). Thus each user can have several accounts. Below is sample data:

In relational databases, such data is usually stored in different tables, but when it comes to importing files, they are often combined in one file. Below is the example of such file:

As you see, some data from the first table is duplicated: the file contains more than one row per user. That's what we call "complex" table. To import such data we have to know which fields uniquely identify a user. In our example it can be "First + Last".

To import such kind of data through GDIE you have to select the other, "The central table contains several lines per target object [Multifragment import]" radio-button, option: 

There is no need to specify explicitly which fields identify an object: GDIE automatically assumes these are the fields that take part in type's matching criteria (about matching criteria see below).

Practically, complex data sources are possible in two cases:
    - when we import objects along with their multifragments (Computer + Hard disks)
    - when we import objects along with their 1->N, N->M relations (User + Accounts).
GDIE supports both scenarios.

<?xml version="1.0" standalone="yes"?>

<NewDataSet>

<update4u>

<Users ID="2" First="Vlad" Last="Novgorodov" Address="11 Pobedy av., app. 23" Status="active"/>

<Users ID="3" First="Alexey" Last="Klimenko" Address="23 Lugovaya str., app. 12" Status="in preparation"/>

<Users ID="4" First="Nick" Last="Vergush" Address="5 Lebedeva str., app. 87" Status="inactive"/>

</update4u>

</NewDataSet>

<?xml version="1.0" standalone="yes"?>

<NewDataSet>

<update4u>

<Users>

<ID>2</ID>

<First>Vlad</First>

<Last>Novgorodov</Last>

<Address>11 Pobedy av., app. 23</Address>

<Status>active</Status>

</Users>

<Users>

<ID>3</ID>

<First>Alexey</First>

<Last>Klimenko</Last>

<Address>23 Lugovaya str., app. 12</Address>

<Status>in preparation</Status>

</Users>

<Users>

<ID>4</ID>

<First>Nick</First>

<Last>Vergush</Last>

<Address>5 Lebedeva str., app. 87</Address>

<Status>inactive</Status>

</Users>

</update4u>

</NewDataSet>

<?xml version="1.0" standalone="yes"?>

<NewDataSet>

<update4u>

<Users ID="2" First="Vlad" Last="Novgorodov" Address="11 Pobedy av., app. 23" Status="active"/>

</update4u>

<OtherData Number="3445" Locked="true"/>

<update4u>

<Users ID="3" First="Alexey" Last="Klimenko" Address="23 Lugovaya str., app. 12" Status="in preparation"/>

<Users ID="4" First="Nick" Last="Vergush" Address="5 Lebedeva str., app. 87" Status="inactive"/>

</update4u>

</NewDataSet>

Source Columns Definition

Select a source table from the dropdown (tables list was defined on previous step; if "Alias" was specified it will be shown).
Select one of the radio buttons - Table Column, Static Column, Source, or Transformation. The view of the detailed window changes its view depending on the selected option. 

Select the "Source" radio button to be able to add an additional mapping of type Source, which you will need to do if your source has an attribute which is not present in your schema (see Example 1). 

Select the "Transformation" radio button to be able to specify an ASQL Transformation Expression for the destination attribute. 

Select the "Static Column" radio button to add a "static" value. It is similar to specifying "SELECT *, '1' as SampleValue" in your source query.

If the "Table Column" or "Source" radio button is selected, select a source column from the drop-down list. The list of columns is obtained by executing the schema query against the source table.

Destination Attributes Definition

Select destination data definition from dropdown (it will contain all classes of the destination type). In case of multifragment class you will see the "[multi]" postfix.

Select destination attribute from dropdown (it will contain all simple attributes and relations of the selected class). In case of relation you will see the "[relation]" postfix.

Once you've selected the "relation" attribute additional settings should be specified. As you understand, relation is just a reference to the row of the related table. You can't populate it directly (its value is Guid). But you can match your source value against the value of some "simple" attribute (usually "string" data type) in the related class.

So you will see the related class name (just for information) and have to select some related attribute from dropdown (it will contain only simple attributes of the related class).

If you need more complex match you can enter ASQL expression - just use the "Production" keyword to refer the related class (the screenshots above will produce identical matching criteria, of course it's the simplest expression). The ASQL expression you've entered will be validated using DataLayer methods.

GDIE requires data type of this expression to be "string" (implementation feature). If it's not - then GDIE will internally add "CAST" operator to convert it to string. So normally you shouldn't care about data type. Just ensure that data type of your expression is not one of the following: binary, image, ntext, virtual relation.

Matching Options Definition

There are three types of matching criteria:

• relation - used to populate relation attributes (discussed in previous section);
• type - used to match the whole object;
• multifragment class - used to match multifragments within the contained object.

Type's matching criteria must always be present. Just select the appropriate checkbox to include the rule in matching criteria (it's only available if source table is the central one and destination class is not multifragment).

Multifragment class' matching criteria must be present only if at least one attribute of this class is mapped. Just fill the appropriate checkbox (it's only available if destination class is multifragment).

All checked rules will compose the final matching criteria. 

Imagine that such rules are selected as type's matching criteria:

source column "First" is mapped to destination attribute "FirstName";
source column "Last" is mapped to destination attribute "LastName".

Then final criteria will look something like that ("st" is alias for staging and "pr" is alias for production tables): 

where st.First = pr.FirstName and st.Last = pr.LastName

Mapping Rules Limitations

One source table can be mapped to several destination classes but you can't map several source tables to one destination class.
One source column can be mapped to several destination attributes but you can't map several source columns to one destination attribute.
Only central table columns can take part in type's matching criteria.

Match Criteria to Destination Type

Select the "Use this column to match criteria to the destination type" checkbox available at the bottom of the dialog page when the "Table Column" or "Transformation" radio button is selected

to have the system automatically generate a condition for the matching (which you can modify or expand by adding an ASQL Expression or creating a complex condition using Groups) at the next, Matching Query, step of the wizard. 

Transformation Logic

We import data from our source to a staging area in the database. Sometimes, however, sources are not in the shape that you can use in our system; they have to be changed. For this, you use a transform. Transformation simply takes the data in the staging area, runs some ASQL Expressions to change it, so you can match it to something in your production system. The steps are: 

1. Import everything to the staging area. 
2. Apply the transform to the staging area so it will generate new content in the staging area; the content will be determined by this ASQL Expression. 
3. When the transform is done, then it does the matching, and then it does the import, following which it imports the result of this ASQL Expression. 

Validation Rules

When you leave the Matching Query page, all the mapping rules are validated. If any warnings present, they will be shown at this page in the "Review validation details" textbox.

Validation means the following checks:

1. If class has mandatory attribute without default value and this attribute wasn't mapped then such class can't be inserted.
2. If mandatory class can't be inserted then the whole object can't be inserted.

For example, hadn't we mapped the "LastName" attribute we would see the following warning:

Such warning doesn't prevent sequence from execution but available actions are limited now.

Grouping Complex Table

The dropdown "Select destination data definition/relation which corresponds to one row in the source table" is shown only if you selected "The central table contains several lines per target object [Multifragment import]" option at the table definition step. 

When a table contains more than one row per destination object, this can be because information about multifragment class or 1->N (N->M) relation presents in the table. Thus we can say that each row in this table corresponds to one row in the appropriate data definition/relation. And this data definition/relation you have to select from the dropdown.

In our sample file we have more than one row per user. This is because accounts information presents there. Thus each row in the file corresponds to one account and we select "User.Accounts" relation.

During a complex table processing GDIE internally groups records by the type's matching criteria. It means that it stores final matching criteria for each row in a dictionary. If it finds another row with the same criteria it considers it's the same object. GDIE ignores all data in such row except fields mapped to the selected multifragment class/relation (in our example it will be two fields - "Account" and "Domain" - mapped to the "User.Accounts" relation). So only these fields are inserted/updated (in case of multifragments) or used to update a relation.

There's a special option "any mapped multifragment data definition" in this dropdown. If you choose it then all fields mapped to a multifragment class will be processed in a duplicate row. 

If you haven't mapped any multifgrament classes but use complex table then duplicate rows will be skipped simply.

Available Actions

Here you can adjust exact actions (insert, update) that GDIE will perform. 

In the list of available actions you will always see the "type" entry (that means actions for the whole type) and all mapped multifragment data definitions. You can specify required actions separately for each multifragment data definition (with some limitations).

1. Update - specify, whether you want to update matched records. It's checked by default.
2. Insert - specify, whether you want to insert not matched records or skip them. 
   You can't check this option for the "type" if "Object insert is impossible" warning is present in validation details. 
   You can't check this option for the "data definition" if "class can't be inserted" warning is present in validation details. 
   If you check this option for the "type" you must check this option for all mandatory data definitions.

Special Attributes

You can also adjust actions on the attribute level. If you want special processing for some attributes, just specify them in the appropriate grid. Only mapped attributes will be shown.

For each attribute you can select one of the following actions:

• Insert only - attribute's value can only be inserted (when a row is inserted) and is never updated, 
• Update only - attribute's value can only be updated (source value is ignored when a row is inserted)
• Skip - attribute's value is neither inserted nor updated (it can be useful if you only use it for matching)
• Any action - the same as if you don't specify an attribute at all.

Sequence Execution

When you go to the last page you can:

• run sequence as a whole: just click the Execute button,
• run sequence step by step,
• close the wizard by clicking the Close action (you can run the sequence later because it's saved already).

If you choose to run sequence immediately you will see dialog similar to this:

Current state of the import is displayed in the State textbox.
You can abort the execution at any time by clicking the "Abort" action.
Summary information about import is displayed in the Progress textbox.
Data import consists of three major steps (see "Components interaction" chapter):

• populate staging tables;
• match records;
• import records.

When clicking the Execute button, you simply execute them one by one. But you can also execute them separately if you select the Execute import definition step by step radio button.

Note that when you run sequence step by step, the progress is displayed separately for each step, not for the whole sequence execution.

Possible results are:

• full success - import completed without any warnings
• partial success - import completed with warnings (e.g. some records were skipped, some relations didn't match etc.)
• failure - import failed. It means none of the records were imported.

After import has completed click Close action to close the wizard. 

Below are some technical details about import steps.