Migrating from Ant SQL Task to Scriptella ETL.

Developers/DBAs.

This how-to is intended to simplify migration from Ant-based DB creation scripts to Scriptella ETL files. As the result you can get the following benefits:

• Possibility to upload binary files from any URL. Example: INSERT INTO Data_Table(ID, File) VALUES(1, ?{file 'pagefile.sys'})
• Built-in support for non SQL datasources like CSV or LDAP Directories
• Interoperability between several databases(or other datasources) in a single file. This means you can query one DB and insert the results into another one or export the result set to a CSV/HTML/... report file
• Easy redistribution, you just have to put your ETL file, scriptella.jar (less than 500K) and required drivers to run your scripts in any environment with JRE installed. Run your scripts from command-line, Ant or any other java environment.
• User friendly GUI wizards to run your ETL files in interactive mode. Not supported yet.

• Read the Tutorial.
• Download Scriptella binary distribution.

The simplest case

Here is a simple example of Ant SQL task:

<sql driver="org.database.jdbcDriver" url="jdbc:database-url"
    userid="sa"  password="pass" classpath="lib/driver.jar">
	INSERT INTO Table VALUES (1,2,3,4);
	INSERT INTO Table VALUES (2,3,4,5);
</sql>
			

The Scriptella equivalent is the following:

			
<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection driver="org.database.jdbcDriver" url="jdbc:database-url" 
    user="sa" password="pass" classpath="lib/driver.jar"/>
    <script>
	INSERT INTO Table VALUES (1,2,3,4);
	INSERT INTO Table VALUES (2,3,4,5);
    </script>
</etl>