Scriptella ETL, from SQL Server to PostgreSQL

Posted on Wednesday, 22 August 2012


Download Scriptella and unpack it in some folder:


From GNU/Linux in our .bashrc

#Scriptella things
export SCRIPTELLA_DIR="/home/user/applications/scriptella/scriptella-1.0"

This will give us access to the from any directory. We need the  respective drivers from SQL Server and PostgreSQL to achieve a successful connection. At this time we can download the SQLServer driver sqljdbc_4.0.2206.100_enu.tar.gz  and the PostgreSQL driver postgresql-9.1-902.jdbc4.jar

We can put each driver in some directory that will have access from our scriptella scripts, that may be:




or directly in the:


Create the template..

[user@user-pc bea]$ -t
Aug 21, 2012 11:46:23 AM create
INFO: Files etl.xml, have been successfully created.

Now we have two files, I’ll rename and copy the, obtaining two files: and with this contents:

#Scriptella ETL Configuration Properties


#Scriptella ETL Configuration Properties PostgreSQL

We complete the etl.xml

 <description>Scriptella ETL File Template. </description>

<!--Loading from external properties file-->
 <include href=""/>
 <include href=""/>
 <!-- Connection declarations -->
 <connection id="sqlserver" driver="$driver1" url="$url1" user="$user1" password="$password1" classpath="$classpath1"/>
 <connection id="postgresql" driver="$driver2" url="$url2" user="$user2" password="$password2" classpath="$classpath2"/>

<query connection-id="sqlserver">
Select * from big_sql_complecated_stuff

<script connection-id="postgresql">
 INSERT INTO some_table(
 dni, lastname, name, address, dateofbirth, etc)
 VALUES (?1, ?2, ?3, ?4, ?5, ?6);

Then we run, and that is all

[user@user-pc sandbox]$ etl.xml

Aug 21, 2012 2:45:38 PM <INFO> Execution Progress.Initializing properties: 1%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress.Connection id=sqlserver, JdbcConnection{}, Dialect{Microsoft SQL Server 10.00.1600} registered: 3%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress.Connection id=postgresql, JdbcConnection{org.postgresql.jdbc4.Jdbc4Connection}, Dialect{PostgreSQL 7.4.5} registered: 5%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress./etl/query[1] prepared: 10%
Aug 21, 2012 2:45:39 PM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/home/user/sandbox/etl.xml"
Aug 21, 2012 2:45:51 PM <INFO> Execution Progress./etl/query[1] executed: 95%
Aug 21, 2012 2:45:51 PM <INFO> Execution Progress.Complete
Aug 21, 2012 2:45:51 PM <INFO> Execution statistics:
Executed 1 query, 1 script, 9808 statements
/etl/query[1]: Element successfully executed (1 statement). Working time 237 milliseconds. Avg throughput: 4.21 statements/sec.
/etl/query[1]/script[1]: Element successfully executed 9807 times (9807 statements). Working time 12182 milliseconds. Avg throughput: 805.02 statements/sec.
Total working time: 13.02 seconds
Aug 21, 2012 2:45:51 PM <INFO> Successfully executed ETL file /home/user/sandbox/etl.xml
Posted in: ETL, GNU/Linux