Error while executing pentaho transformation (talking to Sql server) through java program

0
votes

I have a Kettle (5.4.0.1) transformation which executes query in MS Sql server 2008 R2 in "Table Input" task. The task executes successfully in the Spoon UI directly or through Job. Same transformation I want to execute through a Java program for which I got this code - 

KettleEnvironment.init();
TransMeta metaData = new TransMeta("first_transformation.ktr");
Trans trans = new Trans( metaData );
trans.execute( null );
trans.waitUntilFinished();

But on executing it in a java program I get this error -

2015/07/30 20:08:34 - TestTransformation - Dispatching started for transformation [TestTransformation] 2015/07/30 20:08:34 - XML Output.0 - Opening output stream in encoding: UTF-8 2015/07/30 20:08:34 - Table input.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : An error occurred, processing will be stopped: 2015/07/30 20:08:34 - Table input.0 - Error occurred while trying to connect to the database 2015/07/30 20:08:34 - Table input.0 - java.io.File parameter must be a directory. [C:\Root\EclipseWorkSpace\TestProject\simple-jndi] 2015/07/30 20:08:34 - Table input.0 - Finished reading query, closing connection. 2015/07/30 20:08:34 - Table input.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Error initializing step [Table input] 2015/07/30 20:08:34 - TestTransformation - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Step [Table input.0] failed to initialize! org.pentaho.di.core.exception.KettleException: We failed to initialize at least one step. Execution can not begin! at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1149) at org.pentaho.di.trans.Trans.execute(Trans.java:607) at TestKettle.main(TestKettle.java:24)

What could be the issue here as the database connection happens successfully while executing through UI and same KTR file I am trying to execute in above code?

2
pentahokettle

2 Answers

0
votes

It seems that the Table Input Step in the Ktr is not able to read the data.

Firstly, if you are calling a ktr file from Java code, make sure that the database driver jar files is properly imported/build inside the Java project. I mean import the "sqlserver" database jar file in the java project.

You may try reading this blog. I have used Maven to handle dependency of jars (my db was : postgresql). And it works :)

Hope this helps :)

0
votes

For anyone else facing this issue - The problem was the folder Simple-Jndi from Pentaho installation directory was required to be placed in the project folder which is not obvious from the error message above although it is mentioned in the error.

2015/07/30 20:08:34 - Table input.0 - java.io.File parameter must be a directory. [C:\Root\EclipseWorkSpace\TestProject\simple-jndi]

After this, I got two other errors which were more clear and was about files missing in the project folder - ESAPI.Properties and validation.properties. I downloaded them from link given here. Then the program ran successfully.