Toolsverse Knowledge Base - New pages [en]

Calling SOAP Web services

2015-02-10T00:21:15Z

Maxlmus:

The ETL Framework natively supports SOAP Web services over HTTP. You will need to:

# Set source or destination connection to the one linked to the SOAP endpoint
# Define SOAP request in the connection CDATA attribute

<syntaxhighlight lang="xml">
<connection alias="Cars">
<url>http://www.vendor.com/soapendpoint</url>
<CDATA><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://www.vendor.com/soapendpoint/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SOAP-ENV:Header>
<ns1:AuthenticationHeader>
<vendorUserId>{requestUserId}</mktowsUserId>
<vendorSignature>{requestSignature}</requestSignature>
<vendorTimestamp>{requestTimestamp}</requestTimestamp>
</ns1:AuthenticationHeader>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<ns1:paramsGetMultipleCars>
<leadSelector xsi:type="ns1:KeySelector">
<keyType>MAKE</keyType>
<keyValues>
<stringItem>Ford</stringItem>
<stringItem>Acura</stringItem>
</keyValues>
</leadSelector>
<batchSize>100</batchSize>
</ns1:paramsGetMultipleCars>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

]]></CDATA>
<params>method=post;exceptions=500;file=false;header_Content-Type=text/xml</params>
<connector>com.toolsverse.etl.connector.xml.XmlObjectConnector</connector>
<transport>com.toolsverse.io.HttpProcessor</transport>
</connection>
</syntaxhighlight>

You can tokenize any part of the CDATA or URL using {token}. In the example above there are 3 tokens:
<pre>
<vendorUserId>{requestUserId}</mktowsUserId>
<vendorSignature>{requestSignature}</requestSignature>
<vendorTimestamp>{requestTimestamp}</requestTimestamp>
</pre>

Simple example below demonstrates how to set tokens, link source to the SOAP and point and transform result web service execution into the destination:

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8" ?>
<scenario>
<name>SOAP</name>
<script>soap</script>
<driver name="com.toolsverse.etl.driver.GenericFileDriver" />
<beforetasks>
<task noconnection="true">
<name>security</name>
<class>com.toolsverse.etl.core.task.common.ScriptTask</class>
<code>
<![CDATA[
importPackage(java.text);
importPackage(java.util);

importPackage(javax.crypto);
importPackage(javax.crypto.spec);

importPackage(org.apache.commons.codec.binary);

importPackage(com.toolsverse.config);

var vendorUserId = SystemConfig.instance().getSystemProperty('requestUserId');
var vendorSecretKey = SystemConfig.instance().getSystemProperty('vendorSecretKey');

var df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
var text = df.format(new java.util.Date());
var requestTimestamp = text.substring(0, 22) + ":" + text.substring(22);
var encryptString = requestTimestamp + vendorUserId;

var secretKey = new SecretKeySpec(new java.lang.String(vendorSecretKey).getBytes(), "HmacSHA1");
var mac = Mac.getInstance("HmacSHA1");
mac.init(secretKey);
var rawHmac = mac.doFinal(new java.lang.String(encryptString).getBytes());
var hexChars = Hex.encodeHex(rawHmac);
var signature = new java.lang.String(hexChars);

SystemConfig.instance().setSystemProperty('requestSignature', signature);
SystemConfig.instance().setSystemProperty('requestTimestamp', requestTimestamp);
SystemConfig.instance().setSystemProperty('requestUserId', vendorUserId);
]]>
</code>
<variables>
<LANG value="JavaScript" />
</variables>
</task>
</beforetasks>
<sources>
<source>
<name>cars</name>
</source>
</sources>
<destinations>
<destination >
<name>cars</name>
</destination>
</destinations>
</scenario>
</syntaxhighlight>

Calling REST based Web services

2014-12-22T16:36:50Z

Maxlmus:

The ETL Framework natively supports REST based Web services. You don't need to do anything special, just make sure your source connection is set to the one linked to the GET endpoint and destinations - to the PUT or POST endpoint.

You can use POST, PUT, GET and DELETE HTTP methods. Read more [[how to customize HTTP connection]].

Use JSON, XML or text to serialize datasets and objects.

You can customize URL using {token}.

For example connection below points to the GET REST endpoint which requires access token:

<syntaxhighlight lang="xml">
<connection alias="Some REST endpoint">
<url>https://host/rest/v1/data/?filter=123&access_token={access_token}</url>
<connector>com.toolsverse.etl.connector.json.JsonConnector</connector>
<transport>com.toolsverse.io.HttpProcessor</transport>
</connection>
</syntaxhighlight>

You will need to set access_token as a system property. Please see examples below:

[https://www.toolsverse.com/products/etl-framework/examples/web_services/http_get.shtml Example of the GET request]

[https://www.toolsverse.com/products/etl-framework/examples/web_services/http_put.shtml Example of the POST request]

Using Eclipse

2014-08-03T15:53:40Z

Maxlmus:

ETL Framework comes with an Eclipse project so you can start playing with a code right away.

To use Eclipse project:

# Open Eclipse
# Select Import->Import->Existing Projects into Workspace. Select Next
# Use "Select root directory" and choose ETL_FRAMEWORK_HOME, for example C:\Toolsverse\ETL Framework
# Click Finish

'''Folders:'''

<pre>
src - source code
src-test - source code for tests
src-examples - source code for examples
lib - libraries
plugin - plugins
license - licenses
config - configuration files
data - data files
data/scenario/Examples - examples of the ETL scenarios
</pre>

Building Source Code

2014-08-03T15:30:35Z

Maxlmus:

== Running build ==

ETL framework comes with a source code for the core components, test suite and build scripts. In order to build ETL framework you need to have Java JDK 7.0 or up and latest version of the [http://ant.apache.org/ Apache Ant].

To build source code execute ant while you are in the ETL Framework home folder. Ant will use ETL_FRAMEWORK_HOME\build.xml.

Example:

<pre>
c:\ant\bin\ant.bat
</pre>

After build run you can find build artifacts under ETL_FRAMEWORK_HOME\dist folder.

== Configuring build ==

To configure build use ETL_FRAMEWORK_HOME\build.properties file. To change build number use ETL_FRAMEWORK_HOME\build.num.

Example of the build.properties files:

<pre>
app-name=Toolsverse API Reference
app-copyright-year=© 2010-2014
app-copyright-company=Toolsverse
app-company-url=http://www.toolsverse.com
app-copyright-url=<a href="http://www.toolsverse.com">Toolsverse</a>
version-num=4.0

compile=true
create-javadoc=false
create-jars=true
sign-jars=false
create-source-jars=true
unit-tests=false

free-edition=false
deploy=etl

debug=on
debuglevel=source,lines,vars
java-level=1.7

jre-min-version=1.7.0

javadoc.api.href=http://download.oracle.com/javase/7/docs/api/
</pre>

== Folders ==
<pre>
src - source code
src-test - source code for tests
src-examples - source code for examples
lib - libraries
plugin - plugins
license - licenses
config - configuration files
data - data files
data/scenario/Examples - examples of the ETL scenarios
</pre>

Installing and Configuring ETL Framework

2014-08-03T12:41:16Z

Maxlmus:

== Downloading ==

Look for clearly marked ETL Framework downloads. On Windows and OS X ETL Framework can be downloaded as an installer. Use archive downloads for other operating systems.

== Installing ==

There are two ways to install ETL Framework: using installer or extracting files from the archive.

When using installer just execute it and follow instructions on the screen.

OS X installer is offered in a form of self-extracting DMG file. When prompted drag and drop ETL Framework folder to the Applications.

When installing from archive you will need to manually extract files using utility provided by operating system. All files are contained in the enclosing folder named etl-version.

Installing from archive will not add any entries to the Start menu, add desktop launchers or register the software in the registry.

== Configuring ==

ETL Framework comes pre-configured to provide highest levels of performance and compatibility.

Framework requires a valid not expired license. Licenses are located under ETL_FRAMEWORK_HOME\license folder (*.lic files) or under ETL_FRAMEWORK_HOME\lib (*.jar files).

'''Note''': ETL Framework comes with a 20-days evaluation license. It will expire after 20 days since the first use. You can a buy a commercial license which will never expire. License can be delivered as a lic or jar file. Using jars for licenses decreases dependency on external folders.

You can read more about licensing [http://www.toolsverse.com/support/faq/ here].

=== Changing configuration properties ===

ETL framework uses etl.properties file located under ETL_FRAMEWORK_HOME/config folder to load configuration properties from. You can change default name of the configuration file by setting property config.file.name. Example: <pre>-Dconfig.file.name=c:/etl/config/test.properties.</pre>

If file does not exist framework will use default values.

You can add any property to the file using format property_name=value. You can also completely ignore configuration file and set properties programmatically using System.setProperty(name, value) or by passing -D props to the Java program.

Some of the properties:
* network.appupdateurl - the URL used to call auto-update Web service
* network.appserverurl - the URL used to call ETL Web service

Example:

<pre>
network.appserverurl=http://localhost:8080/dataexplorer/ide
network.appupdateurl=http://www.toolsverse.com/api/services/CheckForUpdates
# never change properties below
app.update.key=etl
app.name=etlprocess
app.title=ETL Framework
</pre>

You can pass XML configuration file name (or any other -D property) as a command line argument:
<pre>-Detl.config.name=import_data_config.xml</pre>

=== Configuring HTTP Proxy ===

Use your properties file (the default is ETL_FRAMEWORK_HOME/config/etl.properties) to configure HTTP proxy:

<pre>
network.httpproxyhost=
network.httpproxyport=
network.httpproxyuser=
network.httpproxypassword=
# none, http, https, socks
network.httpproxytype=
# comma separated list of hosts to exclude, for example localhost,198.168.1.1. Can leave it blank
network.httpproxyexclude=
</pre>

=== Configuring EMail Notifications ===

If you are going to use email notifications you need to configure mailer. Use your properties file (the default is ETL_FRAMEWORK_HOME/config/etl.properties):

<pre>
# example: smtp.mail.host=smtp.gmail.com
smtp.mail.host=
# example: smtp.mail.port=587
smtp.mail.port=
# example: mailer@gmail.com
smtp.mail.user=
# example: passwd
smtp.mail.password=
# example: ops@gmail.com
smtp.mail.to=
# example: robot@gmail.com
smtp.mail.from=
</pre>

=== Changing JVM Parameters ===

# Open ETL_FRAMEWORK_HOME\etlappstart.properties in your favorite text editor
# Change line containing app.vm.options
# Save

=== Changing Logging Parameters ===

# Open ETL_FRAMEWORK_HOME\config\log4j.properties in your favorite text editor
# Make a change
# Save

=== Adding JDBC drivers ===

Each copy of the ETL Framework comes with the following JDBC drivers:

* DB2
* Informix
* Microsoft SQL Server
* Sybase Advanced Server
* MySQL
* Oracle
* PostgreSQL
* Java DB (Derby)

They are located in the ETL_FRAMEWORK_HOME\jdbc folder. ETL Framework automatically recognizes (on start-up) and dynamically loads (when needed) jdbc drivers under this folder.

To install new jdbc driver just add a sub folder under ETL_FRAMEWORK_HOME\jdbc and put all required files there. Restart Data Explorer.

Alternatively, since JDBC drivers are just jar files you can simply put them in the ETL_FRAMEWORK_HOME\lib folder and they will be automatically loaded and added to the classpath.

== Redistribution and managing dependencies ==

Configure ETL framework the way you like, exclude all unneeded components. You can find redistribution list and recommendations how to manage dependencies [[Redistribution and managing dependencies|here]].

== Checking for updates ==

ETL Framework can check for new version and update itself if new version exists.

To check for new new version run ETL framework executable with -v parameter.

Example:

<pre>
C:\projects\toolsverse\java\dist\etl>etl.exe -v
Toolsverse Etl Framework 4.0-19862. Use -? for help.

Checking for updates...
Update status: new version available
</pre>

To auto update ETL framework run executable with -u parameter.

Example:

<pre>
C:\projects\toolsverse\java\dist\etl>etl.exe -u
Toolsverse Etl Framework 4.0-19862. Use -? for help.
</pre>

== Manually updating ==

You can update framework to the new version manually:

# Download update
# Replace exiting files on files from the update

Running ETL Scenarios

2014-07-31T15:14:31Z

Maxlmus: /* Running ETL scenario using standalone executable */

== Embedding ETL scenario into existing Java application ==

There are three easy steps:

# Instantiate EtlConfig
# Instantiate EtlProcess
# Execute scenario

Example:
<syntaxhighlight lang="java">
public static void main(String[] args)
{
LoadScenarioAndConfigurationFromFile engine = new LoadScenarioAndConfigurationFromFile();

try
{
// instantiates ETL configuration
EtlConfig etlConfig = new EtlConfig();

// set log level to INFO which increases verbosity of the etl engine
Logger.setLevel(EtlLogger.class, Logger.INFO);

// creates embedded ETL process
EtlProcess etlProcess = new EtlProcess(EtlProcess.EtlMode.EMBEDDED);

// print out framework version
System.out.println(SystemConfig.instance().getTitle(
EtlConfig.DEFAULT_TITLE)
+ " "
+ SystemConfig.instance().getSystemProperty(
SystemConfig.VERSION));

// load configuration which contains source and destination
// connections and ETL scenario name. Load and execute ETL scenario.
// If no full path
// provided the configuration file test_etl_config.xml is expected
// to be under app_home/config
EtlResponse response = engine.loadConfigAndExecute(etlConfig,
"test_etl_config.xml", etlProcess);

// print out formatted output from the ETL response
System.out.println(engine.getMessage(response,
"Exampes/Engine/db2file.xml"));

}
catch (Exception ex)
{
System.out.println(Utils.getStackTraceAsString(ex));
}

System.exit(0);
}
</syntaxhighlight>

Look at other [http://www.toolsverse.com/products/etl-framework/java-examples/ examples of embedding ETL engine] into Java application.

== Using Web service to run ETL scenario ==

To run ETL scenario using Web service you need to have ETL server installed, configured, up and running. Please read [http://www.toolsverse.com/products/data-explorer/docs/doc.html#_Installing_Server here] how to install and configure ETL server.

You will also need to specify -Dnetwork.appserverurl JVM option.

Example:

<pre>-Dnetwork.appserverurl=http://host:port/dataexplorer/ide</pre>

You can use ETL_FRAMEWORK_HOME/config/etl.properties to set network.appserverurl or set it programmatically:

<pre>
network.appserverurl=http://localhost:8080/dataexplorer/ide
network.appupdateurl=http://www.toolsverse.com/api/services/CheckForUpdates
app.update.key=etl
app.name=etlprocess
app.title=ETL Framework
</pre>

Once server is configured using Web service is as easy as:

# Instantiating EtlConfig and adding all required connection information
# Instantiating instance and initializing EtlRequest
# Instantiating instance of the EtlService interface using ServiceFactory and appropriate dynamic proxy
# Calling EtlService#executeEtl

Example:
<syntaxhighlight lang="java">

/**
* Creates connection aliases for ETL process, sets scenario name, remotely executes ETL scenario.
*
* @return ETL response
* @throws Exception in case of any error
*/
private EtlResponse execute()
throws Exception
{
// initializes system config, loads properties
SystemConfig.instance();

// instantiates ETL config
EtlConfig config = new EtlConfig();

// initializes ETl config
config.init();

// creates source alias
Alias source = new Alias();
source.setName("Java DB");
source.setUrl("jdbc:derby:{app.root.data}/demo/javadb");
source.setJdbcDriverClass("org.apache.derby.jdbc.EmbeddedDriver");

// creates destination alias
Alias destination = new Alias();
destination.setName("JSON files");
destination
.setConnectorClassName("com.toolsverse.etl.connector.json.JsonConnector");
destination.setUrl("{app.root.data}/*.json");

// adds aliases. ETL process will create connections from these aliases
config.addAliasToMap(EtlConfig.SOURCE_CONNECTION_NAME, source);
config.addAliasToMap(EtlConfig.DEST_CONNECTION_NAME, destination);

// creates empty ETL scenario, sets ETL action. The remote ETL process
// will load it from file at the run-time.
Scenario scenario = new Scenario();
scenario.setName("Examples/Engine/db2file.xml");
scenario.setAction(EtlConfig.EXTRACT_LOAD);

// creates ETL request using given config, scenario and log level
EtlRequest request = new EtlRequest(config, scenario, Logger.INFO);

if (Utils.isNothing(SystemConfig.instance().getSystemProperty(
SystemConfig.SERVER_URL)))
SystemConfig.instance().setSystemProperty(SystemConfig.SERVER_URL,
"http://localhost:8080/dataexplorer/ide");

// gets ETL service from the factory. The ServiceProxyWeb used as a
// dynamic proxy
EtlService etlService = ServiceFactory.getService(EtlService.class,
ServiceProxyWeb.class.getName());

// remotely executes ETL process
return etlService.executeEtl(request);
}
</syntaxhighlight>

== Running ETL scenario using standalone executable ==

# Open APP_HOME/config/etl_config.xml file in your favorite text editor.
# Add connections for the particular ETL scenario
# Specify connections to use and scenarios to run
# Save
# Run ETL executable. For example c:\etl\etl.exe on Windows
# When it is finished check the etl.log file located under APP_HOME/logs

'''Example of the etl_config.xml:'''

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<config>
<connections>
<connection alias="test javadb">
<driver>org.apache.derby.jdbc.EmbeddedDriver</driver>
<url>jdbc:derby:{app.root.data}/demo/javadb</url>
</connection>
<connection alias="test oracle">
<driver>oracle.jdbc.driver.OracleDriver </driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl1</url>
<userid>user</userid>
<password>password</password>
<params/>
</connection>
</connections>

<active.connections>
<sourses>
<source alias="test javadb" />
</sourses>
<destination alias="test oracle"/>
</active.connections>
<execute>
<scenario name="move.xml" action="extract_load" />
</execute>
</config>
</syntaxhighlight>

In this example move.xml ETL scenario located under the {app.data}/scenario folder will be executed using extract_load action. Alias test javadb will be used for the source connection and alias test oracle for the destination.

Read more about [[Configuration_file|ETL Configuration File]].

You can pass properties file name (or any other -D property) as a command line argument:

<pre>etl.exe -Dconfig.file.name=import_data.properties</pre>

You can also set the name of the XML configuration file (connections):

<pre>etl.exe -Detl.config.name=import_data_config.xml</pre>

Last but not least you can use properties file to define XML configuration file (connections):

<pre>
# don't modify
network.appupdateurl=http://www.toolsverse.com/api/services/CheckForUpdates
app.update.key=etl
app.title=ETL Framework
etl.config.name=import_data_config.xml.xml
</pre>

== Creating and running ETL scenario using Data Explorer ==

The best way to create, run and schedule ETL, data integration and data migration scenario is using [http://toolsverse.com/products/data-explorer/docs/doc.html#developetlscenarios Data Explorer] - an integrated ETL IDE.

Configuration file

2014-07-31T13:47:45Z

Maxlmus:

== ETL Framework configuration file ==

ETL configuration file is used to define ETL properties, what ETL scenarios to run and what connections to use. It is located under APP_HOME/config. The default name is etl_config.xml. You can change name of the ETL configuration file using -D property etl.config.name.

Example: <pre>-Detl.config.name=c:/etl/config/test.xml</pre>

File contains the following elements:

=== Properties ===
This section includes Java system properties which can be used by ETL Framework.

'''Example:'''
<syntaxhighlight lang="xml">
<properties>
<log.step>777</log.step>
<cache>com.toolsverse.cache.MemoryCache</cache>
<oracle.oraclehome>c:/oracle</oracle.oraclehome>
</properties>
</syntaxhighlight>

==== List of default properties: ====
{| class="wikitable"
|-
! Property !! Description !! Default value !! Example
|-
| log.step || Log step is a how many rows of the data set to skip until log the event. For example if "log step" property set to 10 while iterating through the rows the ETL process will log every 10 rows.
Note: to log this kind of events the log level must be set to INFO in the log4j.properties located under APP_HOME/config
|| 0 (never) ||<pre><log.step>777</log.step></pre>
|-
| cache || Defines the class which is used by ETL framework to cache values such as primary and foreign keys.

Note: It must implement com.toolsverse.cache. Cache interface
|| com.toolsverse.cache.SynchMemoryCache || <pre><cache>com.toolsverse.cache.MemoryCache</cache></pre>
|-
| connection.factory || Defines the class which is used to create connections used by ETL framework. Note: It must implement com.toolsverse.etl.core.connection.EtlConnectionFactory interface || com.toolsverse.etl.core.connection.EtlConnectionFactoryImpl || <pre><connection.factory>abc</connection.factory></pre>
|-
| oracle.oraclehome || The Oracle client home || || <pre><oracle.oraclehome>c:/oracle</oracle.oraclehome></pre>
|-
| db2.db2home || The DB2 client home || || <pre><db2.db2home>c:/db2</db2.db2home></pre>
|-
| mssql.mssqlhome || The MS SQL Server client home || || <pre><mssql.mssqlhome>c:/sqlserver</mssql.mssqlhome></pre>
|-
| sybase.sybasehome || The Sybase client home || || <pre><sybase.sybasehome>c:/ase</sybase.sybasehome></pre>
|-
| mysql.mysqlhome || The MySQL client home || || <pre><mysql.mysqlhome>c:/mysql</mysql.mysqlhome></pre>
|-
| postgres.postgreshome || The PostgreSQL client home || || <pre><postgres.postgreshome>c:/postgres</postgres.postgreshome></pre>
|-
| informix.informixhome || The Informix client home || || <pre><informix.home>c:/informix</informix.home></pre>
|}

=== Connections ===
In this section you describe SQL and Non SQL connections (for example Excel, XML or text files). There can be as many connections as you want, not necessary the once you are going to use when running particular ETL scenario. Just keep them here, you might need them next time.

'''Example:'''
<syntaxhighlight lang="xml">
<connections>
<connection alias="test javadb">
<driver>org.apache.derby.jdbc.EmbeddedDriver</driver>
<url>jdbc:derby:./data-test/javadb</url>
</connection>

<connection alias="test oracle">
<driver>oracle.jdbc.driver.OracleDriver </driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl1</url>
<userid>user</userid>
<password>password</password>
</connection>
</connections>
</syntaxhighlight>

==== Attributes of the connection node: ====
{| class="wikitable"
|-
! Name !! Description !! Attribute or node !! Example
|-
| alias || The alias name || Attribute || <pre>alias="test javadb"</pre>
|-
| driver || JDBC driver class name || Node || <pre><driver>org.apache.derby.jdbc.EmbeddedDriver</driver>

Note: Can be empty for non SQL connections such as Excel, text, XML</pre>
|-
| url || The URL || Node || <pre><url>jdbc:derby:./data-test/javadb</url></pre>
|-
| connector || The connector class name || Node || <pre>Possible values:
• empty or com.toolsverse.etl.connector.sql.SqlConnector for database connections
• com.toolsverse.etl.connector.excel.ExcelConnector for Excel (*.xls) connections
• com.toolsverse.etl.connector.excel.ExcelXlsxConnector for Excel (*.xlsx) connections
• com.toolsverse.etl.connector.xml.XmlObjectConnector for XML connections
• com.toolsverse.etl.connector.xml.XmlConnector for connections to the Toolsverse dataset XML
• com.toolsverse.etl.connector.text.TextConnector for Delimited and Fixed Length Text connections
• com.toolsverse.etl.connector.json.JsonConnector for JSON connections
• com.toolsverse.etl.connector.html.HtmlConnector for HTML connections
• com.toolsverse.etl.connector.pdf.PdfConnector for PDF connections
</pre>
|-
| autocommit || The auto commit flag for SQL connections (default=false) || Node|| <pre><autocommit>true</autocommit></pre>
|-
| transport || The transport protocol for file-based connections: file,ftp,sftp,http (default=file) || Node|| <pre><transport>ftp</transport></pre>
|-
| userid|| The user name for JDBC connection or remote FTP or SFTP connections || Node|| <pre><userid>user</userid></pre>
|-
| password|| The password for JDBC connection or remote FTP or SFTP connections|| Node|| <pre><password>password</password></pre>
|-
| params|| The connection properties. Use ';' as a delimiter|| Node|| <pre><params>SERVER=ol_svr_custom;DB=etl</params></pre>
|-
| sql|| The SQL which will be executed when connection established|| Node|| <pre><sql>insert into test (abc) values ('123')</sql></pre>
|}

=== SQL Connections ===

Create SQL connection if you need to extract or load data into database which supports SQL. SQL connection uses JDBC or ODBC driver. Driver and url nodes are required when configuring database connection. Other attributes such as userid, password, params, and sql are optional. Connector attribute can be either omitted or set to com.toolsverse.etl.connector.sql.SqlConnector.

'''Example of the SQL connection:'''
<syntaxhighlight lang="xml">
<connection alias="test oracle">
<driver>oracle.jdbc.driver.OracleDriver </driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl1</url>
<userid>user</userid>
<password>password</password>
</connection>
</syntaxhighlight>

If url points to the file or folder in the file system system variables can be used as a part of the url.

=== Non SQL Connections ===

Non SQL connection are used to read data from and create file-based data sources such as XML, JSON, Excel, etc. Url is a required node, everything else is optional. Use '''params''' node to define connection parameters such as delimited for text files, worksheet for excel, etc. Use ";" to separate properties.

'''Example of the Non SQL connection:'''
<syntaxhighlight lang="xml">
<connection alias="test excel">
<url>{app.data}/test.xls</url>
<connector> com.toolsverse.etl.connector.excel.ExcelConnector</connector>
<params>sheetname=Employee;date=MMddyy;datetime=MMddyyyy;time=HH:mm</params>
</connection>
</syntaxhighlight>

'''Common properties for all file-based connections:'''
{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| date || Date format || date=MMddyyyy || MM/dd/yyyy
|-
| datetime|| Date+time format || datetime=MM/dd/yyyy HH:mm || MM/dd/yyyy HH:mm
|-
| time|| Time format || time=HH:mm:ss || HH:mm
|-
| encoding|| Character encoding || encoding=utf-8 || System default for everything, utf-8 for XML
|-
| encode || Encode additional dimension || encode=true || flase
|}

'''Excel properties:'''
{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| sheetname || Worksheet name || sheetname=Test || None
|}

'''Toolsverse XML dataset properties:'''
{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| xsl|| Name of the xlst file || <pre>xsl={app.root.data}/schema/webrowset2dataset.xsl</pre> || None
|-
| xslfrom || Name of the xlst file used to transform from other XML format to XML dataset || <pre>xslfrom={app.root.data}/schema/dataset2webrowset.xsl</pre> || None
|-
| xslto|| Name of the xlst file used to transform to other XML format from XML dataset || <pre>xslfrom={app.root.data}/schema/webrowset2dataset.xsl</pre> || None
|}

'''Delimited text file properties:'''
{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| delimiter|| The field delimiter || <pre>delimiter=';'</pre> || '|'
|-
| firstrow|| Use first row for data || <pre>firstrow=false</pre> || False
|-
| metadata|| Store metadata in XML dataset format || <pre>metadata=false</pre> || False
|-
| charseparator || The character used to enclose string values into || charseparator='"' || None
|-
| lineseparator|| The separator between lines || <pre>
lineseparator=w

Possible values:
• s – os default
• w – windows
• u - unix</pre> || s
|}

'''Filex-lenth text file properties (in addition to the properties above):'''

{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| fields || The length of the each field in the dataset || <pre>fields='6;12;15;8'</pre> || None
|}

'''PDF file properties:'''

{| class="wikitable"
|-
! Property !! Description !! Example !! Default
|-
| pagesize || The page size || <pre>pagesize='A4'</pre> || None
|}

=== Active connections ===
In this section we describe source and destination connections which are going to be used when running particular ETL scenario. There can be multiple source and destination connections. Connection can have a name which must be referenced from the ETL scenario. The default name for the source connection is '''source''' and for destination is '''dest'''.

'''Example of the single source and destination connections with default names:'''
<syntaxhighlight lang="xml">
<active.connections>
<sourses>
<source alias="test javadb" />
</sourses>
<destination alias="test oracle"/>
</active.connections>
</syntaxhighlight>

'''Example of the multiple source and destination connections with user’s defined names:'''
<syntaxhighlight lang="xml">
<active.connections>
<sourses>
<source alias="test excel" name="excel" />
<source alias="test javadb" name="javadb" />
</sourses>
<destinations>
<destination alias="test oracle" name="oracle" />
<destination alias="test xyz" name="xyzcon" />
</destinations>
</active.connections>
</syntaxhighlight>

'''Attributes:'''
{| class="wikitable"
|-
! Attribute !! Description !! Example
|-
| alias|| The name of the alias. Must be the same as in the '''connections''' section|| <pre><source alias="test javadb" /></pre>
|-
| name|| The name of the connection. Name must be referenced from the ETL scenario|| <pre><destination alias="test xyz" name="xyzcon" /></pre>
|}

=== Execute (scenarios) ===
This section contains ETL scenarios which should be executed. You can execute multiple scenarios one by one or in parallel. If one of the scenarios fails the rest will be terminated as well. Each scenario can be executed using different action.

'''Example (single scenario):'''
<syntaxhighlight lang="xml">
<execute>
<scenario name="test.xml" action="extract_load" />
</execute>
</syntaxhighlight>

'''Example (multiple scenarios, different actions):'''
<syntaxhighlight lang="xml">
<execute>
<scenario name="test1.xml" action="extract" />
<scenario name="test2.xml" action="load" />
</execute>
</syntaxhighlight>

'''Example (multiple scenarios, executed in parallel):'''
<syntaxhighlight lang="xml">
<execute>
<scenario name="test1.xml" action="extract_load" parallel="true"/>
<scenario name="test2.xml" action=" extract_load" parallel="true" />
</execute>
</syntaxhighlight>

'''Attributes:'''
{| class="wikitable"
|-
! Attribute !! Description !! Example
|-
| name || Scenario file name.

Note: If scenario file name does not have a folder it is expected to be in the APP_HOME/data/scenario folder.
|| <pre>name="test1.xml"</pre>
|-
| action || <pre>The ETL action. Possible actions:
• extract – only extract
• load – only load
• extract_ load – extract and load</pre>
|| <pre>action="extract"</pre>
|-
| parallel || If set to true the scenario will be executed in the separate thread. Makes sense when there is more than one scenario to execute || <pre>parallel="true"</pre>
|}

== Example of the ETL configuration file ==

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<config>
<properties>
<log.step>1000</log.step>
</properties>

<connections>
<connection alias="test excel">
<url>{app.data}/test.xls</url>
<connector> com.toolsverse.etl.connector.excel.ExcelConnector</connector>
<params>sheetname=Employee;date=MMddyy;datetime=MMddyyyy;time=HH:mm</params>
</connection>

<connection alias="test javadb">
<driver>org.apache.derby.jdbc.EmbeddedDriver</driver>
<url>jdbc:derby:{app.data}/javadb</url>
</connection>

<connection alias="test oracle">
<driver>oracle.jdbc.driver.OracleDriver </driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl1</url>
<userid>user</userid>
<password>password</password>
</connection>
</connections>

<active.connections>
<sourses>
<source alias="test excel" name="excel" />
<source alias="test javadb" name="derby" />
</sourses>
<destination alias="test oracle"/>
</active.connections>
<execute>
<scenario name="test.xml" action="extract_load" />
</execute>
</config>
</syntaxhighlight>

In this example test.xml ETL scenario located under the {app.data}/scenario folder will be executed using extract_load action. Connections excel and derby which linked to the aliases test excel and test javadb will be used as a source connections. Connection linked to the alias test oracle will be used as a destination connection. ETL framework is set to log every 1000 extracted or loaded records.

Redistribution List

2014-07-31T13:35:48Z

Maxlmus:

=== Redistribution list ===

__FORCETOC__

{| class="wikitable"
|-
! Folder !! File !! Required !! Needed for || Depends on
|-
| '''home''' || || '''Yes''' || Root folder ||

|-
| || etl.exe || No || Windows executable ||
|-
| || etl.app || No || Mac application bundle ||
|-
| || etl.sh || No || Unix/Linux shell script ||
|-
| || etlappstart.properties || No || JVM properties ||
|-
| || etl.jar || '''Yes''' || Main executable jar ||

|-
| '''home\config''' || || No || Default configuration folder ||

|-
| || etl.properties || No || Configuration properties ||
|-
| || etl_config.xml || No || Default [[Configuration_file|ETL configuration file]] ||
|-
| || log4j.properties || No || Log configuration ||

|-
| '''home\license''' || || No || Folder for licenses ||
|-
| || *.lic || No || Licenses (ETL framework will also look at license.jar under home\lib folder) ||

|-
| '''home\data''' || || No || Default folder for data ||

|-
| '''home\data\scenario''' || || No || Default folder for ETL scenarios ||
|-
| '''home\data\schema''' || || No || Default folder for XLT schema files ||
|-
| || dataset2webrowset.xsl || No || XSL schema file for Toolseverse XML dataset to [http://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/WebRowSet.html WebRowSet] XML transformation ||
|-
| || webrowset2dataset.xsl || No || XSL schema file for [http://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/WebRowSet.html WebRowSet] XML to Toolseverse XML dataset transformation ||
|-
| || xmldataset.xsd || No || Schema file for Toolsverse XML dataset file format ||

|-
| '''home\lib''' || || No || Folder for core and third party jar files. Files from this folder automatically added to the classpath ||

|-
| || antlr-3.0.1.jar || '''Yes'''|| Core third party file ||

|-
| || antlr.jar || '''Yes''' || Core third party file ||

|-
| || commons-beanutils-1.9.1.jar || '''Yes''' || Core third party file ||

|-
| || commons-codec-1.6.jar || '''Yes''' || Core third party file ||

|-
| || commons-collections4-4.0.jar || '''Yes''' || Core third party file ||

|-
| || commons-dbcp-1.4.jar || '''Yes''' || Core third party file ||

|-
| || commons-logging-1.1.3.jar || '''Yes''' || Core third party file ||

|-
| || commons-math3-3.0.jar || '''Yes''' || Core third party file ||

|-
| || commons-net-2.0.jar || '''Yes''' || Core third party file ||

|-
| || commons-pool-1.5.4.jar || '''Yes''' || Core third party file ||

|-
| || dom4j-1.6.1.jar || '''Yes''' || Core third party file ||

|-
| || httpclient-4.3.3.jar || '''Yes''' || Core third party file ||

|-
| || httpclient-cache-4.3.3.jar || '''Yes''' || Core third party file ||

|-
| || httpcore-4.3.2.jar || '''Yes''' || Core third party file ||

|-
| || httpmime-4.3.3.jar || '''Yes''' || Core third party file ||

|-
| || javassist.jar || '''Yes''' || Core third party file ||

|-
| || json-io.jar || '''Yes''' || Core third party file ||

|-
| || log4j-1.2.16.jar || '''Yes''' || Core third party file ||

|-
| || rsyntaxtextarea.jar || '''Yes''' || Core third party file ||

|-
| || RSyntaxTextArea.License.txt || '''Yes''' || Core third party file ||

|-
| || saxon9he.jar || '''Yes''' || Core third party file ||

|-
| || toolsverse-core.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-common.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-connector-sql.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-connector.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-core.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-driver.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-metadata.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-io.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-license.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-mvc.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-security.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-service.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-storage.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-updater.jar || '''Yes''' || Toolsverse core file ||

|-
| || derby.jar || No || Derby DB JDBC driver ||

|-
| || iText-4.2.0-com.itextpdf.jar || No || Read and write PDF files ||

|-
| || javax.json-1.0.4.jar || No || Read and write JSON files ||

|-
| || javax.mail.jar || No || Send emails ||

|-
| || jsch-0.1.43.jar || No || SFTP client ||

|-
| || poi-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || poi-ooxml-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || poi-ooxml-schemas-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || servlet-api.jar || No || Required if you are going to call Web services ||

|-
| || socks.jar || No || Socks proxy used together with FTP ||

|-
| '''home\plugin''' || || No || Folder for plugins. In other to use files in this folder you must have a valid not expired license ||

|-
| || toolsverse-etl-connector-excel.jar || No || Required if you are going to read and write Excel files || toolsverse-etl-connector-json.jar

|-
| || toolsverse-etl-connector-html.jar || No || Required if you are going to read and write HTML files ||

|-
| || toolsverse-etl-connector-json.jar || No || Required if you are going to read and write JSON files ||

|-
| || toolsverse-etl-connector-pdf.jar || No || Required if you are going to read and write PDF files ||

|-
| || toolsverse-etl-connector-text.jar || No || Required if you are going to read and write CVS and fixed-length files || toolsverse-etl-connector-json.jar, toolsverse-etl-connector-xml.jar

|-
| || toolsverse-etl-connector-xml.jar || No || Required if you are going to read and write XML files || toolsverse-etl-connector-json.jar

|-
| || toolsverse-etl-db2.jar || No || Required if you are going to work with DB2 databases ||

|-
| || toolsverse-etl-informix.jar || No || Required if you are going to work with Informix databases ||

|-
| || toolsverse-etl-oracle.jar || No || Required if you are going to work with Oracle databases ||

|-
| || toolsverse-etl-mysql.jar || No || Required if you are going to work with MySQL databases ||

|-
| || toolsverse-etl-postgres.jar || No || Required if you are going to work with Postgres databases ||

|-
| || toolsverse-etl-sybase.jar || No || Required if you are going to work with Sybase ASE databases ||

|-
| '''home\jdbc''' || || No || Default folder for [[Installing_and_Configuring_ETL_Framework|JDBC drivers]] ||

|}

=== Example ===

<pre>
home
etl.exe
etl.app
etl.jar
etl.sh
etlappstart.properties
config
etl.properties
etl_config.xml
log4j.properties
license
*.lic
data
scenario
schema
dataset2webrowset.xsl
webrowset2dataset.xsl
xmldataset.xsd
lib
antlr-3.0.1.jar
antlr.jar
commons-beanutils-1.9.1.jar
commons-codec-1.6.jar
commons-collections4-4.0.jar
commons-dbcp-1.4.jar
commons-logging-1.1.3.jar
commons-math3-3.0.jar
commons-net-2.0.jar
commons-pool-1.5.4.jar
derby.jar
dom4j-1.6.1.jar
httpclient-4.3.3.jar
httpclient-cache-4.3.3.jar
httpcore-4.3.2.jar
httpmime-4.3.3.jar
iText-4.2.0-com.itextpdf.jar
javassist.jar
javax.json-1.0.4.jar
javax.mail.jar
jsch-0.1.43.jar
json-io.jar
log4j-1.2.16.jar
poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
rsyntaxtextarea.jar
RSyntaxTextArea.License.txt
saxon9he.jar
servlet-api.jar
socks.jar
toolsverse-core.jar
toolsverse-etl-aliasreader.jar
toolsverse-etl-common.jar
toolsverse-etl-connector-sql.jar
toolsverse-etl-connector.jar
toolsverse-etl-core.jar
toolsverse-etl-driver.jar
toolsverse-etl-metadata.jar
toolsverse-io.jar
toolsverse-license.jar
toolsverse-mvc.jar
toolsverse-security.jar
toolsverse-service.jar
toolsverse-storage.jar
toolsverse-updater.jar
xmlbeans-2.3.0.jar
plugin
toolsverse-etl-connector-excel.jar
toolsverse-etl-connector-html.jar
toolsverse-etl-connector-json.jar
toolsverse-etl-connector-pdf.jar
toolsverse-etl-connector-text.jar
toolsverse-etl-connector-xml.jar
toolsverse-etl-db2.jar
toolsverse-etl-informix.jar
toolsverse-etl-mysql.jar
toolsverse-etl-oracle.jar
toolsverse-etl-postgres.jar
toolsverse-etl-sqlserver.jar
</pre>

=== Managing dependencies, files and folders ===

Files and folders marked as '''required''' must exist in order for framework to work. Others are optional.

Framework is tested with the particular versions of the third party libraries (see redistribution list). It is likely framework will work with more recent versions than with older versions.

Jars under '''lib''' and '''plugin''' folders are automatically added to the classpath on start-up if there is an available license. Only commercial components of the ETL framework require license.

You can reduce number of folders ETL framework depends on:
* Move all files from the '''plugin''' folder to '''lib'''.
* Package existing licenses (*.lic files) in the '''license''' folder into jar file and copy jar into '''lib''' folder. Name of the jar file doesn't matter but lic files must be located in the '''com/toolsverse/license''' folder inside jar.
* Files in the '''config''' folder and '''config''' folder itself are not required. You can pass properties to the framework using -Dproperty_name=value.

Framework uses '''HOME/data/''' folder for temporary files, scripts and data files. If folder does not exist it is automatically created.

By default framework is looking for ETL scenarios in the '''HOME/data/scenario folder'''. If you want to change base folder for scenarios use -D property '''scenario.path'''.

Look at the src/com/toolsverse/config/SystemConfig.java how ETL frameworks uses system folders.

Look at the src/com/toolsverse/etl/core/config/EtlConfig.java how ETL frameworks uses ETL specific folders.

Writing ETL scenarios

2014-07-27T19:09:22Z

Maxlmus:

== Basic stuff ==
Any data integration or data migration process can be described as an extract-transform-load (ETL) or extract-load-transform (ELT). With that in mind, writing scenarios becomes a process of splitting task into extracts, loads and transformations. A scenario glues them all together and adds purpose and logic.

[[http://www.toolsverse.com/products/etl-framework/ The ETL engine]] which powers Toolsverse products uses XML-based language to create scenarios. Before reading this page please take a look at [[ETL scenario language]] specification. XML is a natural choice because it enforces a structure: loads follow extracts and transformations follow extracts and loads. That said, you can chose to stream data, so extract and load are combined in one logical operation or run them in parallel so the actual order of extracts and loads is not guaranteed.

The ETL engine makes it possible to concentrate on a task at hand without reinventing the wheel. It hides the complexity of data integration so in most cases the same techniques can be applied when working with any SQL and Non-SQL data sources. It is possible to use a full power of the target database and do things like direct data loads, parallel extracts, etc.

Please take a look at the [http://www.toolsverse.com/products/etl-framework/examples ETL scenario examples].

== Simple data migration scenario ==

Let take a look at the simple data migration scenario:

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<scenario>
<name>Migrate data</name>
<script>migrate_date</script>
<driver>auto</driver>
<sources>
<source>
<name>employee</name>
<extract>
<sql>select * from employee</sql>
</extract>
</source>

<source>
<name>emp_resume</name>
<extract>
<sql>select * from emp_resume</sql>
</extract>
</source>
</sources>
<destinations>
<destination>
<name>employee</name>
<metadata>true</metadata>
</destination>

<destination>
<name>emp_resume</name>
<metadata>true</metadata>
</destination>
</destinations>
</scenario>
</syntaxhighlight>

In this example we:
# Extract all data from employee and emp_resume tables in the source database
# Load data into destination, which can be a database or files
# If destination tables don't exist create them on the fly (<metadata>true</metadata> flag)

What we don't do:
# Specify what kind or source and destination we are working with
# How to connect to the source and destination
# How to create destination tables if they don't exist

== Reading data from file-based data sources ==
In the previous example we created a simple data migration scenario which reads data from database using SQL and loads into another database or file-based data source.

Reading data from file-based data sources as easy as writing:

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<scenario>
<name>Migrate data</name>
<script>migrate_date</script>
<driver>auto</driver>
<sources>
<source>
<name>employee</name>
</source>

<source>
<name>emp_resume</name>
</source>
</sources>
<destinations>
<destination>
<name>employee</name>
<metadata>true</metadata>
</destination>

<destination>
<name>emp_resume</name>
<metadata>true</metadata>
</destination>
</destinations>
</scenario>
</syntaxhighlight>

In this example there is no SQL so engine assumes it needs to read data from the files. It doesn't matter what format files are in, as long as there is a suitable connector.

ETL engine naively supports XML, JSON, CVS, Excel and many other formats.

== Using Transformations ==

If we assume that any source or destination has a dataset behind it adding a transformation is a basically attaching it to the source or destination. Transformations can be chained together.

There are three types of transformations:

# Column transformations: validation, add or exclude column, change column type, calculate column value
# Dataset transformations: set operations such as join, de-duplication, pivot, etc
# Dimension transformations: add dimension and extract dimension

Transformations are event based and can be performed on:
* Before extract or load
* During extract or load (inline transformation)
* After extract or load
* After success or failure

'''Example of column transformations:'''

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<scenario parallel="true">
<name>Column level transformations using JavaScript</name>
<script>column_transformations</script>
<description>Extract data, change column name and value, add columns, remove columns</description>
<driver name="com.toolsverse.etl.driver.GenericJdbcDriver" parent="com.toolsverse.etl.driver.mysql.MySqlDriver"/>
<execute/>
<sources>
<source>
<name>employee</name>
<extract>
<sql>select * from employee</sql>
</extract>
</source>
</sources>
<destinations>
<destination>
<name>employee</name>
<objectname>employee_date</objectname>
<metadata use="true"/>
<load stream="true"/>
<variables>
<HIREDATE nativetype="int" sqltype="2"
code="if ({HIREDATE} != null) {value = new Date({HIREDATE}.getTime()).getFullYear();}"
field="HIREDATE" lang="JavaScript" name="hire_year"/>
<SALARY exclude="true" field="SALARY"/>
<BONUS exclude="true" field="BONUS"/>

<COMPENSATION add="true" nativetype="int" sqltype="2" name="compensation" lang="JavaScript"
code="value = parseInt(dataSet.getFieldValue(currentRow, 'SALARY') + dataSet.getFieldValue(currentRow, 'BONUS'));" />

<test add="true" nativetype="varchar(100)"
sqltype="12" value="abc"/>
<test2 add="true" nativetype="varchar(100)"
sqltype="12" value="aaa"/>
<MIDINIT exclude="true" field="MIDINIT"/>
</variables>
</destination>
</destinations>
</scenario>
</syntaxhighlight>

'''Example of dataset transformations:'''

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<scenario>
<name>Diff datasets</name>
<script>diff</script>
<allow>EXTRACT_LOAD</allow>
<sources>
<source noconnection="true">
<name>data_set_1</name>
<extract>
<reader
class="com.toolsverse.etl.connector.excel.ExcelXlsxConnector"
filename="{app.root.data}\demo\matrix.xlsx" sheetname="norm"/>
</extract>
</source>
<source noconnection="true">
<name>data_set_2</name>
<extract>
<reader
class="com.toolsverse.etl.connector.excel.ExcelXlsxConnector"
filename="{app.root.data}\demo\matrix.xlsx" sheetname="norm2"/>
</extract>
</source>
<source noconnection="true">
<name>data_set_only1</name>
<tasks>
<task noconnection="true">
<name>minus</name>
<class>com.toolsverse.etl.core.task.common.Minus</class>
<variables>
<DRIVING value="data_set_1"/>
<MINUS value="data_set_2"/>
</variables>
</task>
<task noconnection="true">
<name>add_column</name>
<class>com.toolsverse.etl.core.task.common.Pivot</class>
<variables>
<FIELDS value="dataset='first only'; first=first; last=last; course=course; year=year; address=address;"/>
</variables>
</task>
</tasks>
</source>
<source noconnection="true">
<name>data_set_only2</name>
<tasks>
<task noconnection="true">
<name>minus</name>
<class>com.toolsverse.etl.core.task.common.Minus</class>
<variables>
<DRIVING value="data_set_2"/>
<MINUS value="data_set_1"/>
</variables>
</task>
<task noconnection="true">
<name>add_column</name>
<class>com.toolsverse.etl.core.task.common.Pivot</class>
<variables>
<FIELDS value="dataset='second only'; first=first; last=last; course=course; year=year; address=address;"/>
</variables>
</task>
</tasks>
</source>

<source noconnection="true">
<name>data_set_12</name>
<tasks>
<task noconnection="true">
<name>intersect</name>
<class>com.toolsverse.etl.core.task.common.Intersect</class>
<variables>
<DRIVING value="data_set_2"/>
<INTERSECT value="data_set_1"/>
</variables>
</task>
<task noconnection="true">
<name>add_column</name>
<class>com.toolsverse.etl.core.task.common.Pivot</class>
<variables>
<FIELDS value="dataset='both'; first=first; last=last; course=course; year=year; address=address;"/>
</variables>
</task>
</tasks>
</source>

<source noconnection="true">
<name>first_and_second</name>
<tasks>
<task noconnection="true" scope="after">
<name>union</name>
<class>com.toolsverse.etl.core.task.common.Union</class>
<variables>
<DRIVING value="DATA_SET_ONLY1"/>
<UNION value="DATA_SET_ONLY2"/>
<TYPE value="union all"/>
</variables>
</task>
</tasks>
</source>

<source noconnection="true">
<name>first_second_both</name>
<tasks>
<task noconnection="true" scope="after">
<name>union</name>
<class>com.toolsverse.etl.core.task.common.Union</class>
<variables>
<DRIVING value="FIRST_AND_SECOND"/>
<UNION value="DATA_SET_12"/>
<TYPE value="union all"/>
</variables>
</task>
</tasks>
</source>

</sources>
<destinations>
<destination noconnection="true">
<name>RESULT</name>
<source>FIRST_SECOND_BOTH</source>
<load>
<driver name="com.toolsverse.etl.driver.GenericFileDriver"/>
<writer
class="com.toolsverse.etl.connector.excel.ExcelXlsxConnector"
filename="{app.root.data}\demo\matrix-output.xlsx" sheetname="output"/>
</load>
</destination>
</destinations>
</scenario>
</syntaxhighlight>

== Using database specific features ==

There are multiple techniques available to developers. In the example below we are using Oracle sql*plus and sql*loader to extract and load data.

<syntaxhighlight lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<scenario>
<name>Oracle Extract using sql*plus and Load using sql*loader</name>
<script>oracle_sql_loader</script>
<driver>com.toolsverse.etl.driver.oracle.OracleDriver</driver>
<allow>EXTRACT_LOAD</allow>
<variables>
<NAME label="Property Name:" value = "name" />
</variables>
<tasks>

<task>
<name>drop_staging_table</name>
<connection>dest</connection>
<class>com.toolsverse.etl.core.task.common.SqlTask</class>
<sql>
DROP TABLE config_property_stage;
</sql>
<onexception action="ignore"/>
</task>

<task>
<name>create_staging_table</name>
<connection>dest</connection>
<class>com.toolsverse.etl.core.task.common.SqlTask</class>
<sql>
create TABLE config_property_stage
(
CONFIG_PROPERTY_NUM NUMBER(18),
NAME VARCHAR2(255),
DESCRIPTION VARCHAR2(255)
);
</sql>
</task>

<task>
<name>config_property_extract</name>
<connection>source</connection>
<class>com.toolsverse.etl.core.task.oracle.OracleExtractTask</class>
<sql>
select CONFIG_PROPERTY_NUM || '|' ||
NAME || '|' ||
DESCRIPTION
from config_property
where upper(name) like upper('%{NAME}%')
</sql>
</task>

<task>
<name>load</name>
<connection>dest</connection>
<class>com.toolsverse.etl.core.task.oracle.OracleLoadTask</class>
<sql>
LOAD DATA
INFILE '{DATA_FOLDER}config_property_extract.dat'
APPEND INTO TABLE config_property_stage
FIELDS TERMINATED BY "|"
trailing nullcols
(
CONFIG_PROPERTY_NUM,
NAME,
DESCRIPTION
)
</sql>
</task>

</tasks>

<destinations>
<destination>
<name>cfg_props</name>
<metadata>true</metadata>
<cursor table="config_property_stage" onfinish="drop" />
</destination>
</destinations>
</scenario>
</syntaxhighlight>

== Things to do ==
Typically a full blown data integration process includes at least extract and load. When everything said and done data from the source are moved to the destination. In some cases it makes sense to split process in steps which can be performed in the different time frames. For example you can:
# Extract data and store them in some intermediate format, for example XML.
# Load data sometime later.

'''Example''':

1. Extract

<syntaxhighlight lang="java">
EtlConfig etlConfig = new EtlConfig();

etlConfig.setAction(EtlConfig.EXTRACT);

EtlProcess etlProcess = new EtlProcess(EtlProcess.EtlMode.EMBEDDED);

EtlResponse response = engine.loadConfigAndExecute(etlConfig,
"test_etl_config.xml", etlProcess);
</syntaxhighlight>

2. Load

<syntaxhighlight lang="java">
EtlConfig etlConfig = new EtlConfig();

etlConfig.setAction(EtlConfig.LOAD);

EtlProcess etlProcess = new EtlProcess(EtlProcess.EtlMode.EMBEDDED);

EtlResponse response = engine.loadConfigAndExecute(etlConfig,
"test_etl_config.xml", etlProcess);
</syntaxhighlight>