0

I have Spoon and I am transferring Data through MySQL to SQL server on daily basis. I want to schedule Spoon file(.ktr and .kjb) ,How to schedule that file.

I Created a Package IN SSRS and tried it scheduling through SQL SERVER AGENT JOB but its giving an Error,manually data is getting transfer in the SQL server tables,but I need it to schedule in morning 4 AM. When i am making scheduling in SQL AGENT its showing error in job history that

"Job Failed Job was Invoked by USER SA,Next step was step 2".

I want to schedule the job so that in every morning I can Transfer data from Mysql to SQL Server.

Pரதீப்
  • 88,697
  • 17
  • 124
  • 160
Khokae
  • 3
  • 1
  • 9

2 Answers2

0

To schedule a job use kitchen and to schedule a transformation use pan. The executable pan.bat and kitchen.bat (resp. pan.sh and kitchen.bat for linux) were shipped with your PDI and sits in the same directory as your spoon.bat (resp. spoon.sh).

Write a new mySchedule.bat file (resp a .sh on linux) with one instruction:

pdi-path\pan.bat /file=myPath\myTransfo.ktr /log=pdi-path\logs\myTransfo.log

or 

pdi-path\kitchen.sh /file=myPath\myJob.kjb /log=pdi-path\logs\myTransfo.log

In the above pdi-path is the folder containing your spoon.bat, which also contains pan.bat and a sub-folder called logs where pan will write the logs. Care also that myPath\myTransfo.ktr is the absolute path to be executed.

You can check the above if typing in a commad line window: absolute-path\mySchedule.bat (mind the path of your bat file). If you do not see anything, it is normal: the logs are written in the log file. You can nevertheless follow the progression of the transformation from sql-server and type (or edit) the log file when the run is finished.

Then open the Windows Task Scheduler, select the cycle, and when it comes to define the action, put the absolute path of your mySchedule.bat.

Check (and clean) the logs for a week or so. By that time you'll have certainly learned how to include a date in the log file name.

I also recommend to enable the logging on the transformation, which will give you the number of input/output lines and other precious information. To know how to do that, ask an other question.

AlainD
  • 5,899
  • 3
  • 16
  • 30
  • ,Could you please elaborate me the process as i don't know how to schedule a spoon file,could you please tell me the whole process and how to write bat file for spoon – Khokae Oct 11 '17 at 05:11
  • I guess you are on a distant server. linux or windows? – AlainD Oct 11 '17 at 08:09
  • Spoon is a graphic interface. You do not open a screen when you schedule transformation. You use kitchen (for ktr) or pan (for kjb), which does exactly the same as spoon/run, but without opening a graphical window. – AlainD Oct 11 '17 at 08:14
  • i have the KTR and windows,How should i do the automatic Schelling of KTR files.I want to do twice in a day automatic and I don't know the procedure to do it – Khokae Oct 11 '17 at 11:27
  • Where (on which machine) do you plan to schedule the ETL run. For scheduling you need a machine up and running 24/7. It cant be be your own desktop, can it ? – AlainD Oct 12 '17 at 14:25
  • ,yes we have a machine that run 24/7 and i want to schedule,,Could you elaborate in details steps for scheduling as i have not done any scheduling before for ktr's – Khokae Oct 15 '17 at 07:28
  • Let's call that machine THE server. The server is a Windows? Which one Windows7, Windows-Server, other? Did you do scheduling for files other than krt? Which kind of scheduler do you use? If I provide you a bat file, are you able to schedule it? Do you know how to get logs when something goes wrong? – AlainD Oct 16 '17 at 09:42
  • Also (stupid question) is the Pentaho Data Integrator (kettle) installed on THE server ? – AlainD Oct 16 '17 at 09:44
  • windows 8..My machine never get shutdown and I want the sscheduler in day time one at 10 AM and second at 16.00 in evening.Yes i need the lo file if something get wrong..I am not running any kind of scheduler from my machine – Khokae Oct 16 '17 at 10:59
  • Ok, we are progressing. For Windows8, the scheduler (which is Windows not Kettle) is called Task Scheduler and it's use is explained here https://www.digitalcitizen.life/how-create-task-basic-task-wizard. Learn how to schedule a task and come back to my answer. – AlainD Oct 17 '17 at 12:34
  • I too have widows server 2017,I can also schedule the task,I went to link in that he was scheduling a .exe file..for scheduling I need to write the Script for kettle, how can I do that ?? – Khokae Oct 23 '17 at 12:29
  • The script for kettle is: `pan.bat /file=mytransfo.ktr` (one line). As explained spoon.bat is named pan.bat for transformation in scripts, and it needs (at least) one parameter: the filename ot your transfo. – AlainD Oct 24 '17 at 13:41
  • i wrote this --E:\Software\pent\data-integration\kitchen.sh \pan.bat /file= E:\Software\pent\data-integration\kitchen.sh\Test.ktr /log= E:\Software\pent\data-integration\kitchen.sh \logs\myTransfo.log....but nothing happen,is this right – Khokae Nov 13 '17 at 08:53
  • IS THIS RIGHT,I AM NOT ABLE TO FIND THE DATA – Khokae Nov 13 '17 at 14:54
0

Yes I succeded in Scheduling of file i.e .KTR File for KTR You need PAN this is the script and put it into your spoon directory saved it as .Bat

cd /e E:\Software\Stablepent\data-integration pan.bat "/file:E:\Spoon_Jobs\T_TEST.ktr" > runlog.txt

Explanation---My spoon is in E drive so E:\software.... My .KTR file is in another folder so i gave the path for file and saved and put it into spoon folder only and then run,A blank window should pop up and disappear not long after.

Khokae
  • 3
  • 1
  • 9