DataTransfer

A console command tool for extracting, transforming and loading/synching/merging data in diffrent database systems and data format types.

Description

The datatransfer is an ETL console command tool. Its using job config files to define its jobs. It supports ADO.Net data sources, LDAP and some file data formats like JSON, XML or CSV. Non-SQL data sources can be used with SQL similar syntax.

Main features are

• Available in .net 4.8 (Windows only) and .Net 7 (Win, Mac, Linux - Linux and Mac not tested)
• ADO.Net data sources: ODBC, OLE, provider for DB2, MSSQL, Oracle, ...
• nonSQL: XML, JSON, CSV, LDAP
• nonSQL: SQL like syntax Joins, where statements, DSL-language for expressions, loading from files and URLs
• HCL Notes data sources
• workflow functions: Pre/Post Statements, PreConditions, check % data amount diffrence between source/target with abort, working with staging tables, variables
• Parallelism
• Transaction handling
• Synchronizing of data rows/columns with subsets on both on source and target
• Synchronizing with timespan columns for only newest data
• Logging with debug level and mails on error (if configured)
• Small footprint, small binaries, no application server
• Usable in command line and therefore easy to integrate into other workflow engines
• Job descriptions in xml with existing xsd schema
• Password encryption in job descriptions user/server based
• Because of open source, extensible to your needs
• Very few dependencies to other libraries

Quick Start

• Decide which version to use (.Net 4.8 or 7.0) -> install respective .NET runtime if not installed already
• Download binaries
• Write your first job (see documentations, examples, xsd-file) - be sure to have a csv file on sourceTable-location with a header line and at least the columns col1, col2 and some data filled for every column

   <?xml version="1.0"?>
   <TransferJob xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation ="job.xsd">
    <transferBlock name="CSV_CSV-Transfer_Protocol"
      conStringSourceType="Custom.CSV" conStringSource="delimiter=,"
      conStringTargetType="Custom.CSV" conStringTarget="delimiter=;" disableTransaction="true">
        <TransferTableJob targetTable="C:\temp\myCSVTarget.csv" identicalColumns="true">
           <customSourceSelect> SELECT col1, col2 from C:\temp\myCSVSource.csv </customSourceSelect>
        </TransferTableJob>
    </transferBlock>
  </TransferJob>

• An editor with XSD support is helpful to get context help on writing the job file, i.e. Visual Studio code with plugin „XML“ from „Red Hat“ https://marketplace.visualstudio.com/items?itemName=redhat.vscode-xml
• Examples of connection strings can be found for instance at https://www.connectionstrings.com/
• Start/Test the tool: datatransfer.exe -f [fullpath to your job.xml]
• to get more debug infos: datatransfer.exe -f [fullpath to your job.xml] -d
• to get full list of parameters: datatransfer.exe -? or datatransfer.exe -h
• Customizing the source code is done with Visual Studio Community or higher Visual Studio versions

Documentation

• Documentation can be found in the documentation folder
  • Content contains user documentation, images and xsd-files in english and german
  • Help contains german code documentation (classes, methods, parameters, ...)
• documentation pages in english can be found here

Planned features / enhancements

• English documentation and tutorials
• Adding ado providers by config, without recompile of the solution
• Maybe combination of .net4 and .net5+ logic in a .Net Standard 2.0 dll for easier mantainability (currently 99,8% identical)
• Authentication scenarios for http/s non-SQL (i.e. XML, JSON, CSV) data sources

Dependencies

Datatransfer .Net4 depends on

• DevLib.csv
• Newtonsoft.JSON Datatransfer .Net7 depends additionally on
• Google.ProtoBuf

Depending of your usage: the third party ADO.Net drivers used.

For .Net 4.8: If you don´t use some shipped ADO.Net drivers you can just remove them.

The Visual Studio project uses a Sandcastle Helpfile Builder project to create documentation. If this plugin isn´t installed, it won´t load the documentation project.

Structure of this repository

• packages\CryptPassword : A tool to create base64 encrypted strings with System.Security.Cryptography.ProtectedData, which can be used in the job description files
• Documentation\Content : German documentation documents for workflow of processing, UML, DSL documentation and description of structure of the job files
• Documentation\Help : Windows help file and web help for code elements of this project
• UnitTest : Unit Tests
• DataTransfer.Net4 : Source code for datatransfer version 4.8
• DataTransfer.Net5 : Source code for datatransfer version 7.0 (5+)
• msa.DSL : Source code for DSL implementation
• msa.logging : Source code for logging
• msaLotusInterop : Source code for (HCL) Notes interface

Customizing

You can customize the code to fulfill your needs.

• to add an ADO.Net driver just add the driver to the project references (Visual Studio)
  • .Net 4.8 compile it
  • .Net 7 - add a line in program.cs Main method like DbProviderFactories.RegisterFactory("IBM.Data.DB2", IBM.Data.Db2.DB2Factory.Instance); and compile it
• to implement custom logic for a ADO.Net provider (example MSSQLInterface.cs)
  • Create a class under Database which is inheriting from DBInterface and define the properties supportsDataAdapterCommands, supportsParameter and supportsBatchCommands (in doubt just try)
  • reference this class in DBInterface.cs method getInterface by adding a new case like case "myADOProvidername": returnVal = new MyADOInterfaceClass(conString, logger); break;
  • other features which can be added in your class
    • add methods for createDataAdapter, createDBCommandBuilder if the ADO.Net-Factory class doesn´t support this
    • implement own methods for deletion deleteTableRows
    • implement own methods for merge
    • implement getParamName for custom parameter name format for your data source
    • just look into the existing implementations for examples
• to implement custom logic for non-ADO.Net provider (example XMLInterface.cs)
  • Create a class under Database\Custom which is inheriting from CustomInterfaceBase and define the properties/events for
    • define drivername, transactionality, batchsize ... (see current implementations)
    • define handler for reading: fillFromSQLParseTree
    • define handler for writing (if needed): initFillContext, commitFillContext, rollbackFillContext, insertHandler and if needed updateHandler and deleteHandler
    • define if SQL syntax is used and complex from expressions (like http)
    • overwrite base methods if needed like deleteTableRows
  • reference this class in DBInterface.cs method getInterface by adding a new case like case "myADOProvidername": returnVal = new MyADOInterfaceClass(conString, logger); break;
• to implement your own DSL for your own provider (example LDAPInterface.cs class ldapDSL)
  • create a valueProvider inherited from DSLValueProvider which implements how to resolve identifier to values
  • create a functionHandler inherited from DSLFunctionHanlder which implements how to execute functions
  • create a dsl-class inherited from DSLDef which uses the dsl provider and dsl function handler
  • In your data provider overwrite readSelect and use your own dsl
• to implement custom source location for nonSQL with/without Parameters
  • look into the class Model\RemoteRequest method resolveRequest -> there you can add custom protocols and logic for authenticating

Note

This tool was created around 2011 and extended as needed on practical use cases. It is used now for many years for several use cases, like export HCL Notes data to DB2, fill DB2 and MSSQL data warehouses or synchronize production and development environments of some systems. Because of the generic approach there are many possibilites, which can be configured, but never were checked. So please be gracious about use cases which don´t work on first attempt. The good is, they can often be fixed with only a few lines of code.