Create Folder/Subfolder in transformation java script

This Article related to Pentaho Kettle ETL tool, where we have requirement to create folder, sub folder in physical file directory dynamically using java script. Please follow below steps, you will be definitely able to do this.

We have step in job, using which we can create folder ans sub-folders.

Magically we have "Modified Java Script Value" step in transformation, using which we can also create folder and Subfolders in filesystem.

Steps :

• Create new transformation and save it
• Add - "Table Input" - "Select Values" - "Modified Java Script Value" in transformation
• Table Input step contains - "select 'temp_folder' as path"
• Select Values Contains entry of field Path
• Modified Java Script Value contains - createFolder('e:/'+path);
• Now save and execute it

Get the list of all tables using JDBC driver using kettle

This is very very beautiful article for me, i bet you guys also enjoy this. The thing is i need information of all tables and columns and keys available in some database and i need all this information at one place and in some different server or some different database.

I searched on google and thanks god i am able to catch up a transformation, which will provide you list of tables, columns, data type of fields, available primary_keys , foreign keys and indexes available with in the database

We can easily get all these information using JDBC connection

Download the transformation from below link

http://wiki.pentaho.com/display/EAI/JDBC+Metadata

List and used function:

1. data types output - function used getTypeInfo()
2. tables output - function used getTables()
3. columns output - function used getTables()
4. primary key output - function used getPrimaryKeys()
5. imported key output - function used getImportedKeys()
6. index info output - function used getIndexInfo()

I have modified this transformation since i need information related to tables and columns only, and end result will be saved in tables of different database

Define Variable for Database Connection in kettle

Instead of using static Database connection name in spoon UI, We can define database connection variable using kettle.properties file, where we can assign dynamic connection which will be available in through out
the kettle JVM space. Once you need to update the ketlle.properties file to update the connection name or server name, in that case you definitely need to restart your spoon or server. If you do not perform this action then variable contains will contains old connection detail thought the project.

To define variables we need to make change in two files
1. kettle.properties
2. shared.xml

Content of shared.xml

<?xml version="1.0" encoding="UTF-8"?>
<sharedobjects>
  <connection>
    <name>TestConnection</name>
    <server>${DBHostname}</server>
    <type>MSSQL</type>
    <access>Native</access>
    <database>${DBName}</database>
    <port>1433</port>
    <username>${DBUsername}</username>
    <password>${DBPassword}</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
      <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
      <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute>
      <attribute><code>PORT_NUMBER</code><attribute>1433</attribute></attribute>
      <attribute><code>PRESERVE_RESERVED_WORD_CASE</code><attribute>N</attribute></attribute>
      <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
      <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>Y</attribute></attribute>
      <attribute><code>SUPPORTS_TIMESTAMP_DATA_TYPE</code><attribute>Y</attribute></attribute>
      <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
    </attributes>
  </connection>

Content of kettle.properties
#TestConnection
DBHostname=localhost
DBName=XYZ0001
DBUsername=sa
DBPassword=******

Now Close your spoon UI if opened (this action is required to reflect your changes in spoon/UI of ETL tool)
Move to directory **/.kettle
delete any backup files if exists
start spoon.bat file

In above picture you can see the newly added variable in shared.xml file and the variable values available in kettle.properties file.