Duplicating a job in Pentaho Data Integration for different connections

Question

I've generated a job via the Copy Tables wizard in Spoon UI, that copies some tables from an oracle database source to an SQL Server one, and made some changes to the job as well.

Now I want to duplicate the same job (same tables and same changes), but changing just the connexions. Is that possible in Spoon ?

I've looked through the Spoon UI and didn't find any option that lets me duplicate the job with changing connexions.

EDIT
After I created the two steps: one for generating rows and the other for obfuscating passwords, In the encrypted field, I do not get the 'Encrypted : Obfusctaed Password' output as expected

here is what the step generate rows looks like :

and here is an other picture for the Modified Java Script Value :

Endoblast

You need to make a copy of your kjb file. Jobs and transformations are in fact XML files. You can then edit it manually. This is pretty straight-forward, with <connection> tags so you should be able to figure it all out by yourself. I find it the fastest way if you want to keep two jobs instead of changing db connection credentials every time. – Iover

How should I go about providing the passwords for the connexions because I notice that they are encrypted in the .ktr files – Endoblast

I've attached it in my answer. – Iover

I'm confuse. Why you can't just copy-paste your files and change the connections directly in the "Database connections" at the left menu? You could just create the new connections or edit the existing ones. Are your transformations/jobs file-based or they're in a repository? Do you want to change them dynamically every now and then? – Strictly

looks like doesn't find that javascript package. Maybe using an old pentaho version or an incomplete one? :s – Schlenger

Answer 1

You need to make a copy of your kjb file. Jobs and transformations are in fact XML files. You can then edit it manually.

This is pretty straight-forward, with <connection> tags so you should be able to figure it all out by yourself.

I find it the fastest way if you want to keep two jobs instead of changing db connection credentials every time.

If you need to provide an obfuscated password (they are not encrypted, just obfuscated) you can create a transformation that will obfuscate it for you providing you the value to put into XML file.

Steps to reproduce creating a transformation for obfuscating passwords in Kettle 6.1 (for older versions the name of the Script Values / Mod step is Modified Java Script Value):

1. Step Generate rows with just 1 row storing password as value
2. Step Script Values / Mod for basic obfuscation

Iover

Couldn't find the Script Values / Mod step, which version of pdi you're using ? – Endoblast

I was using PDI 6.1. This is a regular Javascript step though (probably for you it's called Modified Java Script Value) – Iover

Hey, @Endoblast drop me a comment to let me know how you are handling things and is there anything you are still struggling with – Iover

Hey @Kamil G. I've successfully followed the steps, where can I view my encrypted passwords ? – Endoblast

@Endoblast in the Execution Results panel which is visible on the screen attached to my answer at the bottom left corner. – Iover

Add ; after var encrypted = '?' in Script Values / Mod step. – Iover

Is it working now? I'm asking since you've opened a bounty on this. – Iover

Yes the password part is Ok, all i have to do now is creating a CSV input for my passwords and XML output for each one ( with standard transformations parts ) I'll work on this. I reopened the bounty to give it to your answer :) I think I must wait 24 hours before doing this – Endoblast

Sure :) If you find yourself in trouble doing that feel free to drop a comment and I'll do my best to assist you. – Iover

Thank you very much Kamil !! You're awesome :) – Endoblast

Answer 2

There is example in $KETTLE_HOME/samples/transformation/job-executor. Pass connection parameters to sub-job

Bad thing u cant pass jdbc driver name so, they have to be same type of database with different connection settings

Sidneysidoma

could you please give more details ? – Endoblast

unluckily this doesn't help me because I have different database types :) – Endoblast

Maybe you could using the "Connection Type": Generic Database and passing "Custom Driver Class Name" as a parameter (e.g. MS SQL Server "Custom Driver Class Name": net.sourceforge.jtds.jdbc.Driver, "Custom Connection URL": jdbc:jtds:sqlserver://localhost:1433/;instance). – Schlenger

Answer 3

There is no way to do what you want directly from Pentaho, and one option is to directly alter the transformation's XML to change connections. So the idea is the following:

1. Figure out how the connection's XML will look like. For this just register a new connection, use it somewhere in your transformation and watch the XML source code for element like ........
2. Make a physical copy of your transformations
3. Replace connection definition and reference in the XML file. For this you may use XSLT like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <!-- This template will replace the connection definition -->
    <xsl:template match="connection[./name='SOURCE_CONNECTION_NAME']">
    <!-- This is the connection configuration --> 
            <connection>
                <name>TARGET_CONNECTION_NAME</name>
                <server>localhost</server>
                <type>ORACLE</type>
                <access>Native</access>
                <database><!-- DB NAME --> </database>
                <port>1521</port>
                <username><!-- USERNAME --> </username>
                <password><!-- PWD --></password>
                <servername/>
                <data_tablespace><!-- --></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>PORT_NUMBER</code><attribute>1521</attribute></attribute>
                  <attribute><code>PRESERVE_RESERVED_WORD_CASE</code><attribute>Y</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>
    </xsl:template>
    <!-- And that one will replace the connection's reference in table input/table output --> 
    <xsl:template match="connection[text()='SOURCE_CONNECTION_NAME']">
            <connection>TARGET_CONNECTION_NAME</connection>
    </xsl:template>
</xsl:stylesheet>

Emolument

How should I go about providing the passwords for the connexions because I notice that they are encrypted in the .ktr files – Endoblast

@mounaim, I've written this. Create some empty transformation, put a "Table input" there and specify the needed connection for this step. Save the transformation somewhere, open the .ktr file and just copy the entire <connection> tag contents including the encrypted password. – Emolument

Answer 4

Ah, I do believe you can do this however I have not done it myself as of yet. No need. But I believe you can use shared objects to get this kind of functionality that you want and just have the one (much easier to maintain) transformation. Here's a forum link where it's discussed.

Let me know how it works out. Pretty curious.

http://forums.pentaho.com/showthread.php?75069-Fully-Dynamic-Database-Configuration-Including-underlying-databsae-type

Nonsense