0

I have a few ms SQL scripts which I would like migrate to kettle. Ideally what I would like to do is for each step of the script to be a single step in kettle. But I am finding it difficult to wrap my head around the ms SQL statements and the related kettle step. Could someone please elaborate on which kettle step which can be used to do the following:

  1. select * from [table] - This one is obviously [Input->Table input]
  2. ALTER TABLE [table] ADD [fieldname] [nvarchar](255)

  3. UPDATE b SET b.b_field = a.a_field FROM [table_a] a INNER JOIN [table_b] b ON right(b.b_identity,19)=a.a_identity where b.b_field is null

Step 3 is repeated with many other different tables with different fields compared.

Thank you.

Abdullah Zainul Abidin
  • 65
  • 8

2 Answers2

1

You can't simply translate it step by step. Replace the functionality, but you can't simply map SQL steps to PDI steps. It's a completely different paradigm.

nsousa
  • 4,448
  • 1
  • 10
  • 15
0

As quick and dirty way to migrate SQL scripts to Kettle, you have the SQL Execute script step, in which you can copy/paste your script as is.

Still on the quick and dirty way, note that you can put more than one statement in the Table Input, provided they are separated by coma. You can even create temporary table with SELECT INTO, index them, and read from them.

But obviously this is not really clean. For (2), you can produce a flow containing the table-name and field-name, then use a Javascript step to write down a column containing the text "ALTER TABLE [table-name] ADD [field-name] NVARCHAR(255)", then a Dynamic SQL row to execute that statement for each input line.

For (3) the principle is to create the input flow with a Table Input with a "SELECT a.a_field FROM [table_a] a INNER JOIN [table_b] b ON RIGHT(b.b_identity,19)=a.a_identity". And then to update table_b with an Update step. I cannot really help there since I do not see the b-key for the update.

When this is done and tested for one table and one field, you can put these values in parameters, and use a Job to loop over the parameters.

You have an example of this use case in the sample directory which was shipped with your distribution. It sits in the same folder as your spoon.bat, and the job of interest to you is samples/transformations/dynamic-table/Dynamic table creation and population.kjb.

AlainD
  • 6,187
  • 3
  • 17
  • 31