Import
Marshal provides a context aware way to model your data import. It's based on tying the import rules directly to the export structure. Marshal import modeling eliminates the need to traverse the exported data during import and, moreover, it allows you to migrate data between data models that differ in structure.
To each exported data element, i.e. harvested data, you can tie a sequence of import statements. Each import statement can use any exported data, or even the output of earlier import statements, as its input.
In order to import, you need a target database. This means that you need to refer to the target system, e.g. check your data warehouse, business intelligence, archival or search solution for details on how to load data into it. If there's no target yet, i.e. if the target is being developed as part of the transfer process, the Marshal Editor can provide one for you.
Read more on how to generate a target database.
Example Model
We will exemplify how to create and use import statements using the Music model, see image 1.
Image 1, Example model
We start by using the model to harvest information about artists, their releases, tracks and lyrics using public web services. After harvesting, we want to store the information in our own database. For more information about the Music model, please read the
Xml Web service harvester article.
Add import statements
Select a query node, and in the Import section, select the Rules property and click on the ellipsis button to display the Import Rules form.
Image 2, The import rules property
Enter the connection string to the target database. The connection string will be used as a default value for the statements in the Inport Rules for the query node, i.e. you can have different connection strings for different statements if you wish. This means that one set of Import Rules can insert the data into multiple databases.
Click on the magic wand and select New INSERT Statement.
Image 3, Add a new Insert statement
Select the table into which you want to insert data. The columns of the selected table are automatically listed in the data grid. Select/check the columns into which you want to insert data. In our example, we have chosen not to insert data into the id column, since it is an autoincremental identity column. Click OK to create the insert statement.
Image 4, The Insert Wizard
The statement is displayed in an sql editor. You can alter the statement in any way you find necessary. The sql flavour allowed depends on your target database. In the datagrid at the bottom of the form you bind data from your model to the sql statement. If the name of the query node leaves match the names of the columns in the sql statement, the leaves will automatically be sugested for you. In this case, where we
generated the database model from the Marshal model, all column names match and hence all parameters are automatically bound correctly.
Image 5, Bind Parameters
After we have inserted an artist, we need to select the row id of the inserted row, in order to be able to relate the releases to the artist. We have chosen to select MAX(id), since this sql-syntax will work for all the major relational databases. The result of the statement is bound to the variable ArtistID.
Image 6, Bind result to variables
We are now done with the Artist query-node. Select the Release query node, open the Import Rules form and add your statements. The connection string is inherited from the import rules of the parent query element, i.e. from the Artist node. You can change the connection string if you wish. Click on the magic wand button and select New INSERT Statement. When inserting the releases for an artist we need to bind the Artist row id, which we bound to the variable ArtistID, to the Artist column in the import statement, see image 7 below.
Image 7, Using bound data
Repeat the steps under, the Add import statements section, for the remaining query nodes.
Test run your import statements
You can test your import logics in the Marshal Editor. See our article on
manual execution for more details.
