Saturday, May 7, 2016

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.

No comments:

Post a Comment

web stats