Replicating To a CSV Flat File

When replicating from a relational database to a flat file, Syniti Replicate allows you to replicate data in two different ways, or modes:

  • Refresh
    A one-time complete replication from source table to CSV format flat file, according to replication settings and scripts. You can control the timing of the replication, identify the columns to be replicated and add scripts to transform data during replication.

  • One-way mirroring
    A continuous update of a replicated table to a CSV flat file based on changes to the source database that have been recorded in the database server log. You can define the replication settings to check the transaction log on the source database at regular intervals. Any changes found in the log would be applied to the target file.

    To define a target connection for CSV output:

    1.  In the Metadata Explorer, choose Targets, then Add New Connection from the right mouse button.

    2. In the Add Target Connection wizard, type a name for the connection and choose Files - CSV in the Database field.

    3. In the Set Connection String screen Output Folder field, enter the path to a directory to contain the output files and schema.

    4. Set properties to configure the format of the CSV output files as needed.

    5. Set a value for the Add Transactional Info field. Choose Yes to include transactional information in the output for each record obtained from the database. This can be useful if replicating using mirroring from the source database.

    6. Optionally set a value for the Use One File Per Group field. False generates a single output file for each replicated table. True generates one file for all replicated tables in a group.

    7. You can leave the ExtendedProperties field blank.

    8. Click Next to view the Select Tables screen.
      If this is the first time you have created a File connection using the output folder defined above, the table display will be empty. You can add a representation of target tables after completing the wizard.

    9. Click Next to display the Actions screen,

    10. Optionally choose to continue with creating replications once the wizard is complete.

    11. Click Next to display the summary, then click Finish to create the connection.

    12. The next step is to add target output representation to the Metadata Explorer. This will be represented as relational tables.

    Now you can set up replications from whichever source connection you have defined to the target file.

Flat File Structure

Files used as a replication target have the following structure. The file has a .mir extension when used for mirroring and a .ref extension when used for snapshot or refresh replications.

Column

Value

1

Sequence number

2

Timestamp of transaction

3

User, if provided by the database

4

Table name

5

Operation code – one of I (Insert), U (Update) or D (Delete)

6

Previous primary key value for the database column. This is used for update operations to identify the correct record.

7

Next primary key value. This is used for update operations to identify the correct record in cases where the primary key is changed during the update operation.

8

Database column value

Example File

Example filename: corp_20150115T161123155064_1.mir

10,2015-01-22 15:32:39.000000,,"glb","I",,2,"test insert"

11,2015-01-22 15:32:40.000000,," glb ","D",10,,

12,2015-01-22 15:32:42.000000,," glb ","U",104,104,"my updated"

13,2015-01-22 15:32:44.000000,," glb ","U",1,12,"sd"