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

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
Move to directory .kettle
delete any backup files if exists
start spoon.bat file

No comments:

Post a Comment

web stats