Sunil Reddy Enugala

…..is just walking through MSBI Street.

Execute Java Class file from SSIS – Part 3

Posted by Sunil Reddy Enugala on June 4, 2013

Recently we have discussed about two processes of executing Java Class file.

Execute Java Class file from SSIS

Execute Java Class file from SSIS – Part 2

Let’s discuss about another process to execute Java Class file from SSIS.

Process 3:

  • In this process we will use C# code to execute Java Class file
  • This process is as flexible as process 1
  • Knowledge of C# is pre-requisite Drag a Script Task component to the SSIS Control Flow Designer pane

Drag a Script Task component to SSIS Control Floe designer pane.

Create five SSIS variables as follows.

                             image

  • FilePath: Path of the file which need to be deleted.

         In our example Filepath value is D:\\FileDeletion\\DellmeSoon\\DelmeSoon.txt

  • JavaClass: Path of the Java Class file. Value is D:\\FileDeletion\\DeleteFile.class
  • JavaDir: Path of the java.exe application.

Value is C:\\Program Files\\Java\\jdk1.7.0_17\\bin\\java.exe

  • WorkingDir: Folder path of the java.exe application.

We need to enable the EvaluateAsExpression property of WorkingDir variable and  set the expression as shown in the below figure.

SUBSTRING( @[User::JavaDir] , 1, LEN( @[User::JavaDir] )-FINDSTRING(REVERSE(@[User::JavaDir]),"\\",1)+1) 

                                    image

  • ExecStatement: Contains Query syntax to execute Java Class file.

We need to enable the EvaluateAsExpression property of ExecStatement variable and  set the expression as shown in the below figure.

"-classpath " + SUBSTRING( @[User::JavaClass] , 1, LEN( @[User::JavaClass] )-FINDSTRING(REVERSE(@[User::JavaClass]),"\\",1)+1) + " "+SUBSTRING (@[User::JavaClass], LEN( @[User::JavaClass] ) – FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1) + 2,  LEN (RIGHT( @[User::JavaClass], FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1 ) – 1 ) ) – FINDSTRING( REVERSE( @[User::JavaClass] ), ".", 1 )  ) + " "+ @[User::FilePath]

                            image

Now double click on Script task and select ExecStatement, JavaDir and WorkingDir as Script task ReadOnlyVariables as shown in below figure.

                      image

click on Edit Script to write the script to execute Java Class file as shown in below figure.

image

 

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;

namespace ST_18e16a8ea284440083cdac12d6812db0.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0"
            , Publisher = "", Description = "")]
    public partial class ScriptMain : 
    Microsoft.SqlServer.Dts.Tasks.ScriptTask
                      .VSTARTScriptObjectModelBase
    {
        private int ExecuteProcess(string cmd
                                    , string cmdParams
                                    , string workingDirectory
                                    , int timeout
                                    , out string stdOutput)
        {
            using (Process process = Process.Start(
                     new ProcessStartInfo(cmd, cmdParams)))
            {
                process.StartInfo.WorkingDirectory = workingDirectory;
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                process.Start();
                stdOutput = process.StandardOutput.ReadToEnd();
                return process.ExitCode;
            }
        }
        public void Main()
        {
          //  Dts.TaskResult = (int)ScriptResults.Success;
            string stdOutput;
            string varStatement = 
                        (string)Dts.Variables["ExecStatement"].Value;
            string varWorkingDir = 
                        (string) Dts.Variables["WorkingDir"].Value;
            string varJavaDir = 
                         (string)Dts.Variables["JavaDir"].Value;                
            int myTimeout = 5;
            int exitCode = new ScriptMain().ExecuteProcess(
                                             @varJavaDir
                                             ,varStatement
                                             ,@varWorkingDir
                                             ,myTimeout
                                             ,out stdOutput);
            Console.WriteLine(stdOutput);
        }
    }
}

Now build the script and close the editor. Click Ok on Script Task wizard.

                           image

Now press f5 to execute the Script task.

                       image

Let me know your comments and queries.

About these ads

One Response to “Execute Java Class file from SSIS – Part 3”

  1. Anonymous said

    Nice one. Thank you it helped me lot. Saved lot time

Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 659 other followers

%d bloggers like this: